Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
От | Gerasimenko |
---|---|
Тема | Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT |
Дата | |
Msg-id | 45744F10.5000903@gmx.net обсуждение исходный текст |
Ответ на | Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT (Teodor Sigaev <teodor@sigaev.ru>) |
Список | pgsql-ru-general |
a tak ? (index po : n_traffic.collect_time, n_logins.account_id) SELECT collect_time FROM n_traffic, n_logins WHERE collect_time is not null AND n_traffic.login_id = n_logins.login_id AND account_id = '1655' order by collect_time limit 1; > SELECT > collect_time > FROM > n_traffic, > n_logins > WHERE > collect_time > '1970-01-01 00:00:00' > AND > n_traffic.login_id = n_logins.login_id > AND > account_id = '1655'; > > А так не лучше? C индексами по login_id в обоих таблицах... > > Anton wrote: >> Слышал, что ещё со времён 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) >
В списке pgsql-ru-general по дате отправления:
Следующее
От: AntonДата:
Сообщение: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )