Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT - Mailing list 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)
>