Как ускорить запрос Postgresql?

Question

[Dannerty]

Подскажите, как можно ускорить запрос?
Изначальный запрос оптимизировал примерно в 2 раза, но все равно слишком долго обработка идет:
Индексы:
"i_table_1_primarykey" btree (primarykey)
"i_table_1_starttime" btree (starttime)
"i_table_2_session" btree (session)
Поля session и primarykey в формате uuid, starttime - дата и время

SELECT "t1".* FROM table_1 as t1, (select session from table_2 group by session) as t2 WHERE t1.primarykey=t2.session ORDER BY "starttime" DESC LIMIT 20;

EXPLAIN ANALYZE

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=516423.42..516423.47 rows=20 width=64) (actual time=6916.209..6916.216 rows=20 loops=1)
   ->  Sort  (cost=516423.42..516602.29 rows=71549 width=64) (actual time=6916.208..6916.213 rows=20 loops=1)
         Sort Key: t1.starttime DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Hash Join  (cost=217922.29..514519.53 rows=71549 width=64) (actual time=2212.324..6773.452 rows=823467 loops=1)
               Hash Cond: (table_2.session = t1.primarykey)
               ->  Group  (cost=0.43..243099.63 rows=71549 width=16) (actual time=1.119..3483.992 rows=823468 loops=1)
                     Group Key: table_2.session
                     ->  Index Only Scan using i_table_2_session on table_2(cost=0.43..227046.60 rows=6421212 width=16) (actual time=1.117..2939.527 rows=6228279 loops=1)
                           Heap Fetches: 517761
               ->  Hash  (cost=108137.05..108137.05 rows=4723505 width=64) (actual time=2205.657..2205.657 rows=4523121 loops=1)
                     Buckets: 131072  Batches: 64  Memory Usage: 7475kB
                     ->  Seq Scan on table_1 t1  (cost=0.00..108137.05 rows=4723505 width=64) (actual time=0.791..1125.886 rows=4523121 loops=1)
 Planning time: 1.232 ms
 Execution time: 6916.313 ms
(15 строк)

Shared_buffers = 2GB
work_mem пробовал менять значения, но особо роли не сыграло
Подозреваю, что проблема в HDD, и надо бы перевести сервер на SSD, но возможно есть какой-либо еще вариант.

Comments

[Евгений]

А почему t2.session не unique? Это ведь ключ сессии, похоже, — значит, должен быть unique, и group by тогда не нужен.

Вообще, для начала надо бы explain отформатировать в вопросе правильно, а то читать невозможно. Еще лучше использовать https://explain.depesz.com/

И почитайте документацию на PostgreSQL, кстати. Потому что параметр work_mem на данный запрос не может оказывать почти никакого влияния — важно понимать какой параметр на что влияет.

[Dannerty]

Евгений, в table_2 представлены данные о действиях(primarykey), совершенных во время одной сессии(session). поэтому существуют записи с одинаковым значением session

[Евгений]

Dannerty, точно, наоборот прочитал. Все равно, исправьте вопрос, невозможно же читать. В тег code надо вывод завернуть. Или ссылку на explain привести.

[lega]

Похоже индекс i_table_1_starttime не используется, а он должен быть ключевым, t2 похоже только для фильтрации.
Как вариант, сделать bool колонку вместо t2, и по ней фильтровать, тогда будет летать за милисекунды (зависит от данных).

> Подозреваю, что проблема в HDD, и надо бы перевести сервер на SSD, но возможно есть какой-либо еще вариант.
Если индексы влязят в память то это не влияет.

Answer 1

[Melkij]

Нужен explain (analyze,buffers), сильно желательно с track_io_timing = on в конфиге.

параметр work_mem на данный запрос не может оказывать почти никакого влияния

Оказывает. Если планировщик решит, что work_mem не хватает - не будет hash join. Для сортировки 27кб, очевидно, без разницы.

Впрочем, зачем вам вообще join подзапроса где одна только группировка подзапроса занимает половину времени ответа? Вам простой exists нужен.

SELECT "t1".* FROM table_1 as t1
where exists (select 1 from table_2 as t2 WHERE t1.primarykey=t2.session)
ORDER BY "starttime" DESC LIMIT 20;

А дальше know your data. Если по starttime DESC быстро находятся нужные exists - будет хорошо. Если exists мало - стоит подумать, а не денормализовать ли этот признак в table_1 с триггером для консистентности в table_2 и частичным индексом по starttime where t2_exists.

Поля session и primarykey в формате uuid

Не очень хорошая идея. Оно и сильно медленнее при сравнении относительно bigint (особенно если речь о varchar, а не uuid типе данных) и из-за случайного распределения несколько сбивает с толку статистику планировщика.

Comments

[Dannerty]

Спасибо! Попробовал сделать через exists, и стало
Planning time: 4.205 ms
Execution time: 120.352 ms

Answer 2

[PrAw]

Вообще не с потгресом работаю, но...
Смущает блок (select session from table_2 group by session)
Получается, Вы берёте (и сортируете!) все номера сессий из таблицы 2, в которой 6 421 212 строк, только ради того, чтобы проджоинить с таблицей 1, у которой 4 723 505 строк. Итого сортируем 6 миллионов строк, чтобы отфильтровать 4 миллиона.

Может быть имеет смысл вывернуть запрос наизнанку:

SELECT * FROM table_1 
WHERE primarykey=(SELECT session FROM table_2 WHERE table_2.session=table_1.primarykey LIMIT 1) 
ORDER BY "starttime" DESC LIMIT 20;

В итоге большую сортировку сессий меняем на штучные проверки (по индексу!) для выбираемых строк в соответствии с датой. Еще можно порезать диапазон дат, чтобы работать с недельным/месячным набором, а не всей историей.

Comments

[Dannerty]

Вариант 2, как и от Melkij с запросом через exists ускорили выполнение в разы, спасибо!

Answer 3

[Макс]

попробуйте
select session from table_2 group by session
заменить на
SELECT DISTINCT session FROM table_2
эффект тот же, но ...

Comments

[Dannerty]

этот вариант я пробовал, видимого эффекта не оказало