Skip site navigation (1) Skip section navigation (2)

Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT

From: Teodor Sigaev <teodor(at)sigaev(dot)ru>
To: Anton <anton200(at)gmail(dot)com>
Cc: pgsql-ru-general(at)postgresql(dot)org
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
Date: 2006-12-04 16:31:36
Message-ID: 45744D68.2030401@sigaev.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
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)

-- 
Teodor Sigaev                                   E-mail: teodor(at)sigaev(dot)ru
                                                    WWW: http://www.sigaev.ru/

In response to

Responses

pgsql-ru-general by date

Next:From: GerasimenkoDate: 2006-12-04 16:38:40
Subject: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
Previous:From: AntonDate: 2006-12-04 16:24:19
Subject: SELECT ... WHERE ... IN (SELECT ...) -> SELECT ... WHERE (... OR ... )

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group