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

Question

[Андрей Суржиков]

База Postgresql (10).

Две таблицы:
researches - исследования (32000 строк), в качестве первичного ключа используется UUIDv4
dicom_files - привязанные к ним файлы (10 млн строк), в качестве первичного ключа используется UUIDv4, так же есть foreign research_uuid.

Запрос с выбором всех dicom_files конкретного исследования стал выполнять очень очень долго

SELECT * FROM dicom_files where research_uuid='ba466dea-dd5e-4b10-8ed2-4f37e6121e97'

(от 13 до 70 секунд)

Анализ дает такой результат:

EXPLAIN (ANALYZE) SELECT * FROM dicom_files where research_uuid='ba466dea-dd5e-4b10-8ed2-4f37e6121e97'

"Gather  (cost=1000.00..626260.62 rows=1094 width=263) (actual time=12146.798..13391.109 rows=3482 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  ->  Parallel Seq Scan on dicom_files  (cost=0.00..625151.22 rows=456 width=263) (actual time=12968.026..13382.436 rows=1161 loops=3)"
"        Filter: (research_uuid = 'ba466dea-dd5e-4b10-8ed2-4f37e6121e97'::uuid)"
"        Rows Removed by Filter: 3586078"
"Planning time: 0.130 ms"
"Execution time: 13391.300 ms"

Как отлаживать подобные проблемы? С чем это может быть связано?

Answer 1

[Vitsliputsli]

Постройте индекс btree для research_uuid.

Answer 2

[TheRonCronix]

1. Может быть bloating таблицы из-за изменений/удалений/втавок. Давно ли делался VACUUM, настроен ли AUTOVACUUM ?
2. Для выборки малого количества строк из таблицы полезно создать b-tree index на поле, по которому происходит фильтрация.
3. Выполняется ли периодический сбор статистики на таблице командой ANALYZE ( по плану отфильтровано 3586078 строк а выбрано ~3000, а выпишете о 10 млн) ? Неверная статистика - прямой путь к неоптимальному плану выполнения.