@Dannerty

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

Подскажите, как можно ускорить запрос?
Изначальный запрос оптимизировал примерно в 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, но возможно есть какой-либо еще вариант.
  • Вопрос задан
  • 806 просмотров
Решения вопроса 2
Melkij
@Melkij
PostgreSQL DBA, рассмотрю предложения о работе
Нужен 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 типе данных) и из-за случайного распределения несколько сбивает с толку статистику планировщика.
Ответ написан
@remzalp
Программер чего попало на чем попало
Вообще не с потгресом работаю, но...
Смущает блок (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;


В итоге большую сортировку сессий меняем на штучные проверки (по индексу!) для выбираемых строк в соответствии с датой. Еще можно порезать диапазон дат, чтобы работать с недельным/месячным набором, а не всей историей.
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
MaxDukov
@MaxDukov
впишусь в проект как SRE/DevOps.
попробуйте
select session from table_2 group by session
заменить на
SELECT DISTINCT session FROM table_2
эффект тот же, но ...
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы