How to debug a Postgresql query?

Question

PostgreSQL

Postgresql base (10).
Two tables:
researches- studies (32000 rows), UUIDv4
dicom_filesis used as the primary key - files bound to them (10 million rows), UUIDv4 is used as the primary key, there is also foreign research_uuid.
A query with a selection of all dicom_files of a particular study has become very very long

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

(from 13 to 70 seconds)
The analysis gives the following result:

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"

How to debug such problems? With what it can be connected?

Answer 1

Build a btree index for research_uuid.

Answer 2

1. Tables may be bloating due to changes/deletions/inserts. Has VACUUM been done for a long time, is AUTOVACUUM configured?
2. To select a small number of rows from a table, it is useful to create a b-tree index on the field that is being filtered.
3. Is the periodic collection of statistics on the table performed by the ANALYZE command (according to the plan, 3586078 rows are filtered and ~ 3000 are selected, and write out about 10 million)? Incorrect statistics are a direct path to a suboptimal execution plan.