Почему тормозит PostgreSQL?

Question

[Арсений Соколов]

Ситуация следующая: в БД есть таблица чтение которое очень долгое, даже с простыми запросами, например:

explain (analyse, buffers) select ad.id, ad.application_id, ad.status_id from public.application_data ad order by ad.created_at desc limit 10;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit  (cost=1132099.43..1132100.59 rows=10 width=20) (actual time=734696.008..734841.610 rows=10 loops=1)                                                    |
|  Buffers: shared hit=245663 read=815789                                                                                                                      |
|  I/O Timings: read=2183039.204                                                                                                                               |
|  ->  Gather Merge  (cost=1132099.43..1646651.22 rows=4410136 width=20) (actual time=734696.005..734841.602 rows=10 loops=1)                                  |
|        Workers Planned: 2                                                                                                                                    |
|        Workers Launched: 2                                                                                                                                   |
|        Buffers: shared hit=245663 read=815789                                                                                                                |
|        I/O Timings: read=2183039.204                                                                                                                         |
|        ->  Sort  (cost=1131099.40..1136612.07 rows=2205068 width=20) (actual time=734475.096..734475.099 rows=8 loops=3)                                     |
|              Sort Key: created_at DESC                                                                                                                       |
|              Sort Method: top-N heapsort  Memory: 26kB                                                                                                       |
|              Worker 0:  Sort Method: top-N heapsort  Memory: 26kB                                                                                            |
|              Worker 1:  Sort Method: top-N heapsort  Memory: 26kB                                                                                            |
|              Buffers: shared hit=245663 read=815789                                                                                                          |
|              I/O Timings: read=2183039.204                                                                                                                   |
|              ->  Parallel Seq Scan on application_data ad  (cost=0.00..1083448.68 rows=2205068 width=20) (actual time=0.024..733551.121 rows=1764296 loops=3)|
|                    Buffers: shared hit=245609 read=815789                                                                                                    |
|                    I/O Timings: read=2183039.204                                                                                                             |
|Planning Time: 23.833 ms                                                                                                                                      |
|Execution Time: 734841.723 ms                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

В таблице немного больше 5 млн. записей.

create table public.application_data
(
    id               serial        primary key,
    code             varchar,
    description      varchar,
    request_url      varchar,
    request_method   varchar,
    request_headers  jsonb,
    request_body     jsonb,
    response_code    integer,
    response_headers jsonb,
    response_body    jsonb,
    application_id   integer        references public.application,
    created_at       timestamp with time zone default now()
);

create index ix_application_data_code
    on public.application_data (code);

create index ix_application_data_request_method
    on public.application_data (request_method);

create index ix_application_data_request_url
    on public.application_data (request_url);

create index ix_application_data_response_code
    on public.application_data (response_code);

Считать аналитику просто невозможно, любое обращение к таблице занимает от 10 секунд до нескольких минут.

Для БД выделено 2 ядра Intel Xeon Gold 6230, 8 ГБ RAM и сетевой SSD.

По аналитике запросов следующая информация:

explain (analyse, buffers) select count(*) from public.application_data;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Finalize Aggregate  (cost=876806.80..876806.81 rows=1 width=8) (actual time=557320.005..557398.903 rows=1 loops=1)                                                                            |
|  Buffers: shared hit=489895 read=685642 dirtied=72                                                                                                                                           |
|  I/O Timings: read=1651378.789                                                                                                                                                               |
|  ->  Gather  (cost=876806.59..876806.80 rows=2 width=8) (actual time=557280.094..557398.885 rows=3 loops=1)                                                                                  |
|        Workers Planned: 2                                                                                                                                                                    |
|        Workers Launched: 2                                                                                                                                                                   |
|        Buffers: shared hit=489895 read=685642 dirtied=72                                                                                                                                     |
|        I/O Timings: read=1651378.789                                                                                                                                                         |
|        ->  Partial Aggregate  (cost=875806.59..875806.60 rows=1 width=8) (actual time=557247.264..557247.267 rows=1 loops=3)                                                                 |
|              Buffers: shared hit=489895 read=685642 dirtied=72                                                                                                                               |
|              I/O Timings: read=1651378.789                                                                                                                                                   |
|              ->  Parallel Index Only Scan using application_data_pkey on application_data  (cost=0.43..870293.92 rows=2205068 width=0) (actual time=111.920..556913.135 rows=1764296 loops=3)|
|                    Heap Fetches: 4026680                                                                                                                                                     |
|                    Buffers: shared hit=489895 read=685642 dirtied=72                                                                                                                         |
|                    I/O Timings: read=1651378.789                                                                                                                                             |
|Planning Time: 255.695 ms                                                                                                                                                                     |
|Execution Time: 557428.491 ms                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Вот некоторые параметры конфигурации БД:

autovacuum_work_mem -1
maintenance_work_mem 268435456
work_mem 4194304
max_parallel_maintenance_workers 2
from_collapse_limit 8

Как я предполагаю, проблема либо в не хватке ресурсов, либо в наличии JSONB полей в таблице. Возможно решение убрать поля или использовать другой тип поля для хранения информации?

Кошмар какой-то получается, намекните, пожалуйста, на варианты решения проблемы.

Answer 1

[Сергей Горностаев]

Почему вы план смотрите не для того запроса, который тормозит? Из очевидного - отсутствие индекса на поле, используемое для сортировки.

Comments

[Арсений Соколов]

Сейчас добавляю индексы для поля сортировки, анализ запроса с сортировкой добавил также в вопрос.

[Арсений Соколов]

В итоге, после добавления индекса на поле сортировки, всё стало значительно быстрее. Время запроса сократилось до нескольких микросекунд.

[mayton2019]

Арсений Соколов, хорошо прогрел кеши. Попробуй на новых данных.