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

> Индекс по ( collect_time, login_id ) не поможет?
>
> Судя по планам, постгрес мучается с выводом join'a - он не сортирован
> по collect_time.

Заранее приношу извинения, если пост «сливается»… Если будет
нечитаемо, сообщите, я переформатирую.

После приведенных ниже операций, ничего практически не изменилось…

=# CREATE INDEX n_traffic_collect_time_login_id
ON n_traffic(collect_time, login_id);
CREATE INDEX

=# VACUUM FULL ANALYZE n_traffic;
VACUUM

explain analyze
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'
 ORDER BY collect_time LIMIT 1
------------------------------------------
 Limit  (cost=0.00..2027.58 rows=1 width=8) (actual
time=4910.153..4910.153 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..740066.55 rows=365 width=8) (actual
time=4910.147..4910.147 rows=0 loops=1)
         ->  Index Scan using n_traffic_collect_time_login_id on
n_traffic  (cost=0.00..9333.04 rows=230814 width=12) (actual
time=0.045..1048.889 rows=230828 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.012..0.012 rows=0
loops=230828)
               Index Cond: ("outer".login_id = n_logins.login_id)
               Filter: (account_id = 1655)
 Total runtime: 4910.451 ms
(8 rows)



=# \d n_traffic
                         Table "public.n_traffic"
    Column    |            Type             |          Modifiers
--------------+-----------------------------+------------------------------
 login_id     | integer                     | not null
 traftype_id  | integer                     | not null
 collect_time | timestamp without time zone | not null default now()
 bytes_in     | bigint                      | not null default (0)::bigint
 bytes_out    | bigint                      | not null default (0)::bigint
Indexes:
    "n_traffic_login_id_key" UNIQUE, btree (login_id, traftype_id, collect_time)
    "n_traffic_collect_time" btree (collect_time)
    "n_traffic_collect_time_login_id" btree (collect_time, login_id)
    "n_traffic_collect_time_month" btree (date_trunc('month'::text,
collect_time))
    "n_traffic_login_id" btree (login_id)
Foreign-key constraints:
    "n_traffic_login_id_fkey" FOREIGN KEY (login_id) REFERENCES
n_logins(login_id) ON UPDATE CASCADE
    "n_traffic_traftype_id_fkey" FOREIGN KEY (traftype_id) REFERENCES
n_traftypes(traftype_id) ON UPDATE CASCADE

--
engineer