Thread: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )
Слышал, что ещё со времён 7.4 производительность запросов типа сабжа
была значительно улучшена... Однако как раз споткнулся об него так:
SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01 00:00:00'
AND login_id IN (SELECT login_id FROM n_logins WHERE account_id = '1655')
ORDER BY collect_time LIMIT 1
Даёт ужас вроде 5 секунд, при этом в планах было вообще более 700 000сек..
Хак типа:
SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01 00:00:00'
AND (login_id = '1240' OR login_id ='411')
ORDER BY collect_time LIMIT 1
даёт всего около 0.3 сек и реальность недалека от плана.
Это я что-то "перепонастроил" или всё и вправду так невесело?
Подробности:
SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01 00:00:00'
AND login_id IN (SELECT login_id FROM n_logins WHERE account_id = '1655')
ORDER BY collect_time LIMIT 1
--------------------------------
Limit (cost=0.00..2028.44 rows=1 width=8) (actual
time=4434.532..4434.532 rows=0 loops=1)
-> Nested Loop IN Join (cost=0.00..722123.09 rows=356 width=8)
(actual time=4434.527..4434.527 rows=0 loops=1)
-> Index Scan using n_traffic_collect_time on n_traffic
(cost=0.00..9723.29 rows=225023 width=12) (actual time=0.036..830.414
rows=224971 loops=1)
Index Cond: (collect_time > '1970-01-01
00:00:00'::timestamp without time zone)
-> Index Scan using n_logins_pkey on n_logins
(cost=0.00..3.15 rows=1 width=4) (actual time=0.010..0.010 rows=0
loops=224971)
Index Cond: ("outer".login_id = n_logins.login_id)
Filter: (account_id = 1655)
Total runtime: 4434.827 ms
(8 rows)
SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01 00:00:00'
AND (login_id = '1240' OR login_id ='411')
ORDER BY collect_time LIMIT 1
--------------------------------
Limit (cost=7.04..7.04 rows=1 width=8) (actual time=0.145..0.145
rows=0 loops=1)
-> Sort (cost=7.04..7.04 rows=1 width=8) (actual
time=0.139..0.139 rows=0 loops=1)
Sort Key: collect_time
-> Bitmap Heap Scan on n_traffic (cost=4.01..7.03 rows=1
width=8) (actual time=0.089..0.089 rows=0 loops=1)
Recheck Cond: (((login_id = 1240) AND (collect_time >
'1970-01-01 00:00:00'::timestamp without time zone)) OR ((login_id =
411) AND (collect_time > '1970-01-01 00:00:00'::timestamp without time
zone)))
-> BitmapOr (cost=4.01..4.01 rows=1 width=0) (actual
time=0.080..0.080 rows=0 loops=1)
-> Bitmap Index Scan on n_traffic_login_id_key
(cost=0.00..2.01 rows=1 width=0) (actual time=0.043..0.043 rows=0
loops=1)
Index Cond: ((login_id = 1240) AND
(collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on n_traffic_login_id_key
(cost=0.00..2.01 rows=1 width=0) (actual time=0.029..0.029 rows=0
loops=1)
Index Cond: ((login_id = 411) AND
(collect_time > '1970-01-01 00:00:00'::timestamp without time zone))
Total runtime: 0.358 ms
(11 rows)
--
engineer
Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )
From
"Alexander M. Pravking"
Date:
On Mon, 2006-12-04 at 21:24 +0500, Anton wrote:
> Подробности:
> SELECT collect_time FROM n_traffic WHERE collect_time > '1970-01-01
> 00:00:00'
> AND login_id IN (SELECT login_id FROM n_logins WHERE account_id = '1655')
> ORDER BY collect_time LIMIT 1
> --------------------------------
> Limit (cost=0.00..2028.44 rows=1 width=8) (actual
> time=4434.532..4434.532 rows=0 loops=1)
> -> Nested Loop IN Join (cost=0.00..722123.09 rows=356 width=8)
> (actual time=4434.527..4434.527 rows=0 loops=1)
> -> Index Scan using n_traffic_collect_time on n_traffic
> (cost=0.00..9723.29 rows=225023 width=12) (actual time=0.036..830.414
> rows=224971 loops=1)
> Index Cond: (collect_time > '1970-01-01
> 00:00:00'::timestamp without time zone)
Здесь явно неразумный план: выборка из большой таблицы всех (видимо)
записей по условию, которое всегда true. Здесь даже seq scan был бы
быстрее (ANALYZE давно делали?).
> -> Index Scan using n_logins_pkey on n_logins
> (cost=0.00..3.15 rows=1 width=4) (actual time=0.010..0.010 rows=0
> loops=224971)
Далее по всем этим найденным записям прогоняется поиск в n_logins.
Поиск сам по себе быстрый, но много-многократный. В итоге
Nested Loop IN Join выше выполняется уже 4 секунды.
> Index Cond: ("outer".login_id = n_logins.login_id)
> Filter: (account_id = 1655)
> Total runtime: 4434.827 ms
> (8 rows)
На мой взгляд, как раз вариант, который предложил Фёдор, должен
использовать более приемлемый join. Можно взглянуть на его EXPLAIN?
И, кстати, во времена семёрки рекомендовалось использовать JOIN в явном
виде, когда оптимизатор выбирал не лучший вариант, то есть задавать
порядок JOIN'а вручную. Насчёт восьмёрки вроде проскакивало, что
оптимизатор умничает даже в случае явного JOIN, хотя я не уверен, так
что можно попробовать и этот вариант.
--
Fduch M. Pravking
Может с этого надо было начать... Словом, потребность есть ВЫБРАТЬ
ПОСЛЕДНЮЮ ДАТУ (поле collect_time) ИЗ ТАБЛИЦЫ ТРАФИКА (таблица
n_traffic) ДЛЯ ВСЕХ ЛОГИНОВ (поле login_id) ЗАДАННОГО АККАУНТА (поле
account_id).
То есть из таблицы n_logins выбрать все login_id которые имеют
заданный account_id, а потом из таблицы n_traffic выбрать самую
последнюю дату из всех этих login_id.
PostgreSQL 8.1.5.
> Здесь явно неразумный план: выборка из большой таблицы всех (видимо)
> записей по условию, которое всегда true. Здесь даже seq scan был бы
> быстрее (ANALYZE давно делали?).
VACUUM FULL ANALYZE делался буквально перед тем как. collect_time НЕ
ВСЕГДА сравнивается с "1970-01-01 ...", а более чаще с датой начала
текущего месяца. Но в определенных случаях (некоторые данные
неизвестны) берется просто тот самый "1970-01-01 ...".
Однако это дела не меняет, если даже убрать вообще условие с
collect_time (см. планы внизу для nestloop ON и OFF).
> На мой взгляд, как раз вариант, который предложил Фёдор, должен
> использовать более приемлемый join. Можно взглянуть на его EXPLAIN?
...
> порядок JOIN'а вручную. Насчёт восьмёрки вроде проскакивало, что
> оптимизатор умничает даже в случае явного JOIN, хотя я не уверен, так
> что можно попробовать и этот вариант.
см. ниже, видимо это как раз то, о чём ты говоришь.
set enable_nestloop=off;
=# explain analyze SELECT
billing-# collect_time
billing-# FROM
billing-# n_traffic,
billing-# n_logins
billing-# WHERE
billing-# n_traffic.login_id = n_logins.login_id
billing-# AND
billing-# account_id = '1655'
billing-# order by collect_time limit 1;
----------------------------------------
Limit (cost=6248.14..6248.14 rows=1 width=8) (actual
time=1473.737..1473.737 rows=0 loops=1)
-> Sort (cost=6248.14..6249.21 rows=430 width=8) (actual
time=1473.732..1473.732 rows=0 loops=1)
Sort Key: n_traffic.collect_time
-> Hash Join (cost=3.54..6229.33 rows=430 width=8) (actual
time=1473.695..1473.695 rows=0 loops=1)
Hash Cond: ("outer".login_id = "inner".login_id)
-> Seq Scan on n_traffic (cost=0.00..4861.66
rows=271966 width=12) (actual time=0.015..804.507 rows=272007 loops=1)
-> Hash (cost=3.53..3.53 rows=2 width=4) (actual
time=0.078..0.078 rows=2 loops=1)
-> Index Scan using n_logins_account_id on
n_logins (cost=0.00..3.53 rows=2 width=4) (actual time=0.033..0.045
rows=2 loops=1)
Index Cond: (account_id = 1655)
Total runtime: 1474.019 ms
(10 rows)
set enable_nestloop=on;
=# explain analyze SELECT
billing-# collect_time
billing-# FROM
billing-# n_traffic,
billing-# n_logins
billing-# WHERE
billing-# n_traffic.login_id = n_logins.login_id
billing-# AND
billing-# account_id = '1655'
billing-# order by collect_time limit 1;
----------------------------------------
Limit (cost=0.00..2026.44 rows=1 width=8) (actual
time=6280.321..6280.321 rows=0 loops=1)
-> Nested Loop (cost=0.00..871369.04 rows=430 width=8) (actual
time=6280.315..6280.315 rows=0 loops=1)
-> Index Scan using n_traffic_collect_time_login_id on
n_traffic (cost=0.00..10352.51 rows=271966 width=12) (actual
time=0.029..1267.549 rows=272007 loops=1)
-> Index Scan using n_logins_pkey on n_logins
(cost=0.00..3.15 rows=1 width=4) (actual time=0.012..0.012 rows=0
loops=272007)
Index Cond: ("outer".login_id = n_logins.login_id)
Filter: (account_id = 1655)
Total runtime: 6280.565 ms
--
engineer
Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )
From
"Alexander M. Pravking"
Date:
On Tue, 2006-12-05 at 11:47 +0500, Anton wrote: > Может с этого надо было начать... Словом, потребность есть ВЫБРАТЬ > ПОСЛЕДНЮЮ ДАТУ (поле collect_time) ИЗ ТАБЛИЦЫ ТРАФИКА (таблица > n_traffic) ДЛЯ ВСЕХ ЛОГИНОВ (поле login_id) ЗАДАННОГО АККАУНТА (поле > account_id). Ммм, если последнюю, тогда уж ORDER BY collect_time DESC :) > То есть из таблицы n_logins выбрать все login_id которые имеют > заданный account_id, а потом из таблицы n_traffic выбрать самую > последнюю дату из всех этих login_id. Только PG делает выборку в другом порядке :) Сначала ищет все записи в n_traffic, а потом отсеивает ненужные, для которых не нашлось требуемой записи в n_logins. > >Здесь явно неразумный план: выборка из большой таблицы всех (видимо) > >записей по условию, которое всегда true. Здесь даже seq scan был бы > >быстрее (ANALYZE давно делали?). > > VACUUM FULL ANALYZE делался буквально перед тем как. collect_time НЕ > ВСЕГДА сравнивается с "1970-01-01 ...", а более чаще с датой начала > текущего месяца. Но в определенных случаях (некоторые данные > неизвестны) берется просто тот самый "1970-01-01 ...". > Однако это дела не меняет, если даже убрать вообще условие с > collect_time (см. планы внизу для nestloop ON и OFF). Всё равно фильтр по дате, судя из условий задачи, не настолько сужает поиск, как фильтр по login_id (одному или нескольким) плюс по дате. У тебя PG почему-то совсем не хочет сначала искать login_id'ы, а потом уже по ним -- записи из n_traffic. > ... > >порядок JOIN'а вручную. Насчёт восьмёрки вроде проскакивало, что > >оптимизатор умничает даже в случае явного JOIN, хотя я не уверен, так > >что можно попробовать и этот вариант. > см. ниже, видимо это как раз то, о чём ты говоришь. Нет, я имел в виду вместо ... FROM n_logins, n_traffic WHERE n_traffic.login_id = n_logins.login_id ... попробовать поменять порядок JOIN'а: SELECT collect_time FROM n_logins l JOIN n_traffic t USING (login_id) WHERE l.account_id = '1655' ORDER BY collect_time LIMIT 1; -- Fduch M. Pravking