Thread: Re: SELECT ... WHERE ... IN (SELECT ...) -> SELECT
Индекс по ( collect_time, login_id ) не поможет?
Судя по планам, постгрес мучается с выводом join'a - он не сортирован
по collect_time.
Anton wrote:
>> 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 в обоих таблицах...
>
> Всё замечательно до тех пор, пока не добавляется ORDER BY collect_time
> LIMIT 1;
> А без (ORDER BY collect_time LIMIT 1) замечательно отрабатывает и Ваш
> вариант, и IN.
>
> Вся штука, что сортировка и лимит почему-то плохо работают с
> указанными вариациями (по сути ведь это JOIN, так ведь...), когда для
> account_id = '...' есть БОЛЬШЕ ЧЕМ ОДИН login_id.
> Когда login_id всего один, все варианты довольно быстры.
>
> На всякий случай:
> engineer@billing=# \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_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@billing=# \d n_logins
> Table "public.n_logins"
> Column | Type | Modifiers
> ------------+------------------------+-------------------------------------------------------------
>
> login_id | integer | not null default
> nextval('n_logins_login_id_seq'::regclass)
> account_id | integer | not null
> login | character varying(255) | not null
> pwd | character varying(128) |
> Indexes:
> "n_logins_pkey" PRIMARY KEY, btree (login_id)
> "n_logins_login_key" UNIQUE, btree ("login")
> "n_logins_account_id" btree (account_id)
> Foreign-key constraints:
> "n_logins_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> n_accounts(account_id)
> Triggers:
> tr_after_n_logins AFTER INSERT OR DELETE OR UPDATE ON n_logins FOR
> EACH ROW EXECUTE PROCEDURE tr_f_after_n_logins()
> tr_before_n_logins BEFORE UPDATE ON n_logins FOR EACH ROW EXECUTE
> PROCEDURE tr_f_before_n_logins()
> --
> engineer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
> Индекс по ( 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