From: | Anton <anton200(at)gmail(dot)com> |
---|---|
To: | pgsql-ru-general(at)postgresql(dot)org |
Subject: | SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... ) |
Date: | 2006-12-04 16:24:19 |
Message-ID: | 8cac8dd0612040824s117ccc9dtbd3f99fd5b1d1a6f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-ru-general |
Слышал, что ещё со времён 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
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Sigaev | 2006-12-04 16:31:36 | Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT |
Previous Message | Антон Пецкус | 2006-10-02 10:00:02 |