POSTGRES EXPLAIN25.12.2024 12:15

Всем привет! На связи Ришат Садыков из Spectr. Сегодня мы поговорим про explain в Postgres. Это объемная тема, по ней можно найти много материала. В статье я постарался собрать только ту информацию, которой достаточно для начала использования explain. Материал поможет эффективно использовать его для повышения производительности запросов тем, кто этим никогда не занимался.

Общая информация про explain

SQL — это декларативный язык, то есть с его помощью мы описываем, какие данные мы хотим получить. Но каким образом они будут получены — решает СУБД.

Планировщик запросов — механизм, который определяет, каким образом будет выполняться запрос.

План запроса — это дерево, состоящее из узлов, отвечающих за выборку строк таблиц и другие операции (агрегирование, соединение, сортировка). Каждый узел имеет оценку стоимости выполнения операции. EXPLAIN выводит по одной строке на каждый узел плана, а также общую оценку стоимости выполнения запроса. Ниже представлен пример плана запроса.

Gather Merge  (cost=262477.81..358808.03 rows=825630 width=400)
Workers Planned: 2
->  Sort  (cost=261477.78..262509.82 rows=412815 width=400)
      Sort Key: a.column_x
      ->  Parallel Hash Join  (cost=6587.33..73403.18 rows=412815 width=400)
            Hash Cond: (a.foreign_key = b.id)
            ->  Parallel Seq Scan on table_a a
(cost=0.00..25147.19 rows=412815 width=368)
                  Filter: (filter_condition = 'value')
            ->  Parallel Hash  (cost=3542.37..3542.37 rows=157437 width=32)
                  ->  Parallel Seq Scan on table_b b  (cost=0.00..3542.37 rows=157437 width=32)
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true

Оценки стоимости вычисляются на основе следующих параметров:

seq_page_cost — стоимость последовательного чтения одной страницы с диска. Страница — это участок на диске размером 8Кб, на котором хранятся данные таблиц.
random_page_cost — стоимость чтения одной произвольной страницы с диска.
cpu_tuple_cost — стоимость обработки каждой строки при выполнении запроса.
cpu_index_tuple_cost — стоимость обработки каждой записи индекса при сканировании индекса.
cpu_operator_cost — стоимость обработки оператора или функции при выполнении запроса.
effective_cache_size — эффективный размер дискового кеша, доступный для одного запроса.

Также для вычисления стоимости используются данные, хранящиеся в каталогах postgres. В представлении pg_stats хранятся данные каталога pg_statistic.

avg_width — это средний размер данных, хранящихся в столбце attname таблицы tablename.
В каталоге pg_class хранятся данные о различных объектах базы данных, в том числе общая информация о таблицах.

В поле reltuples хранится приблизительное число строк в объекте relname.
На основе всех этих данных производятся вычисления в explain.

Например, для total_cost seqscan следующая формула:
total_cost = relpages*seq_page_cost + reltuples*cpu_tuple_cost + reltuples*cpu_operator_cost
Значения для rows берется из reltuples, для width как сумма avg_width для полей, которые участвуют в узле.

Analyse

Каким образом формируются эти данные и поддерживаются в актуальном состоянии? Данные reltuples обновляются командами VACUUM, ANALYZE и несколькими командами DDL, например CREATE INDEX. Данные avg_width обновляются командами ANALYZE и VACUUM ANALYZE.

Виды сканирования

Sequential scan

При последовательном сканировании данные извлекаются напрямую из табличных страниц, минуя индексы, согласно критериям отбора. Если запрос не содержит условий WHERE, извлекаются все строки таблицы. Этот метод эффективен при низкой селективности выборки, то есть когда нужно получить большую часть строк или все строки таблицы, поскольку получение большого числа строк по индексам может оказаться медленнее.

Index scan

Индексный просмотр (index scan) использует индекс таблицы для поиска ключевых значений, которые указывают на соответствующие строки в таблице.

Index only scan

Просмотр исключительно на основе индекса (index only scan) использует данные из индекса без обращения к строкам таблицы. Однако поскольку в индексе нет информации о видимости строк для транзакций, сначала проверяется карта видимости (visibility map). Если строка находится на странице, помеченной в карте, обращение к таблице не требуется. Это снижает объем операций ввода/вывода и повышает производительность, особенно когда данные редко изменяются. Если же карта не подтверждает видимость строки, обращение к таблице неизбежно, что снижает эффективность метода.

Bitmap scan

Bitmap scan создает битовую карту для указания страниц таблицы с нужными строками. После формирования карт к ним применяются операции BitmapAnd/BitmapOr, оставляя только подходящие под условия страницы. Затем данные извлекаются, обращаясь к каждой странице только один раз.

Виды соединения наборов строк

nested loop
Метод «вложенный цикл» перебирает строки внешнего набора и ищет соответствующие строки во внутреннем наборе для их соединения. Этот способ подходит для небольших выборок и поддерживает различные типы условий соединений, включая эквисоединения.
hash join
При соединении хешированием строки одного набора помещаются в хеш-таблицу в памяти, а строки второго набора проверяются на наличие соответствий в ней. Ключом служит столбец, по которому выполняется соединение. Этот метод эффективен для больших выборок, но работает только с эквисоединениями.
merge join
Метод слияния для соединения таблиц требует, чтобы данные были предварительно отсортированы по столбцам, используемым для соединения. Строки из обоих наборов считываются одновременно и сравниваются по ключевым полям. При нахождении совпадений создается объединенная строка. Процесс продолжается до тех пор, пока не будут обработаны все строки. Этот метод подходит для эквисоединений и эффективен для обработки больших объемов данных.

Опция ANALYSE

Можно запустить команду EXPLAIN вместе с опцией ANALYSE, чтобы фактически выполнить запрос и увидеть реальные тайминги и метрики запроса.
Важный момент! При выполнении запросов изменяющих данные нужно оборачивать команду explain в транзакцию и откатывать, чтобы данные в БД не изменились.

EXPLAIN ANALYZE SELECT * FROM table_a a JOIN table_b b ON a.column_x = b.column_x;
Gather  (cost=1014.73..33104.71 rows=49744 width=719) (actual time=16.917..19.460 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Hash Join  (cost=14.72..27130.31 rows=20727 width=719) (actual time=0.913..0.915 rows=0 loops=3)
      Hash Cond: (a.column_x = b.column_x)
      ->  Parallel Seq Scan on table_a a  (cost=0.00..18727.50 rows=284550 width=368) (actual time=0.826..0.827 rows=1 loops=3)
      ->  Hash  (cost=12.10..12.10 rows=210 width=351) (actual time=0.003..0.003 rows=0 loops=3)
            Buckets: 1024  Batches: 1  Memory Usage: 8kB
            ->  Seq Scan on table_b b  (cost=0.00..12.10 rows=210 width=351) (actual time=0.003..0.003 rows=0 loops=3)
Planning Time: 0.220 ms
Execution Time: 19.502 ms

Выше представлен пример вывода команды EXPLAIN вместе с опцией ANALYSE. Мы видим, что у нас появилось общее время выполнения запроса и время выполнения каждого узла, фактическое количество полученных строк, сколько раз выполнялся каждый узел, информация об использовании памяти и т. д.

Примеры

Рассмотрим несколько примеров

EXPLAIN (ANALYZE) SELECT * FROM table_a ORDER BY column_x;

Gather Merge  (cost=2801262.71..3781518.52 rows=8401606 width=368) (actual time=9601.004..11048.372 rows=10081680 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=2800262.69..2810764.70 rows=4200803 width=368) (actual time=9519.614..9716.411 rows=3360560 loops=3)
      Sort Key: table_a.column_x
      Sort Method: external merge  Disk: 337056kB
      Worker 0:  Sort Method: external merge  Disk: 367584kB
      Worker 1:  Sort Method: external merge  Disk: 396920kB
      ->  Parallel Append  (cost=0.00..227469.94 rows=4200803 width=368) (actual time=4.121..8693.221 rows=3360560 loops=3)
            ->  Seq Scan on table_a_1 table_a_1  (cost=0.00..0.00 rows=1 width=368) (actual time=0.009..0.009 rows=0 loops=1)
				  ...
Planning Time: 2482.305 ms
Execution Time: 11238.612 ms

Запрос выполняется 11,2 секунды. Мы видим здесь сортировку с использованием диска и использованный размер памяти Sort Method: external merge: 337056kB. Обычно сортировка выполняется в памяти, но когда размер требуемой памяти превышает определенный лимит, приходится использовать диск. Естественно, сортировка с использованием диска выполняется дольше, так как оперативная память намного быстрее. За размер лимита оперативной памяти отвечает параметр work_mem. Увеличим его до 800Mb и выполним explain еще раз.

SET work_mem = '800MB';
EXPLAIN (ANALYZE) SELECT * FROM table_a ORDER BY column_x;
Gather Merge  (cost=1394157.71..2374413.52 rows=8401606 width=368) (actual time=684.390..2073.879 rows=10081680 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=1393157.69..1403659.70 rows=4200803 width=368) (actual time=639.491..819.908 rows=3360560 loops=3)
      Sort Key: table_a.column_x
      Sort Method: quicksort  Memory: 639232kB
      Worker 0:  Sort Method: quicksort  Memory: 695053kB
      Worker 1:  Sort Method: quicksort  Memory: 699096kB
      ->  Parallel Append  (cost=0.00..227469.94 rows=4200803 width=368) (actual time=0.037..279.005 rows=3360560 loops=3)
            ->  Seq Scan on table_a_1 table_a_1  (cost=0.00..0.00 rows=1 width=368) (actual time=0.008..0.008 rows=0 loops=1)
				  ...
Planning Time: 5.054 ms
Execution Time: 2252.396 ms

В этот раз нам не понадобилась дополнительная память диска. Об этом нам говорит следующая строка — Sort Method: quicksort Memory: 639232kB
Данный пример просто для демонстрации того, как увеличение значения work_mem может ускорить запрос, в котором памяти используется больше, чем текущее значение work_mem. В сложных запросах одновременно могут выполняться несколько операций сортировки и хеширования, и при этом указанный объем памяти может использоваться в каждой операции, прежде чем данные начнут вытесняться во временные файлы. Кроме того, такие операции могут выполняться одновременно в разных сеансах. Таким образом, общий объем памяти может многократно превосходить значение work_mem. Установка значения 800Mb может привести к проблемам с нехваткой оперативной памяти, поэтому нужно осторожно подбирать это значение в зависимости от ресурсов сервера.

Рассмотрим еще один запрос:

EXPLAIN (ANALYZE) SELECT * FROM schema_a.table_b WHERE user_id = 1 ORDER BY column_y;

Gather Merge  (cost=36081.79..63169.48 rows=232164 width=368) (actual time=31.333..72.066 rows=278594 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=35081.77..35371.97 rows=116082 width=368) (actual time=19.472..24.289 rows=92865 loops=3)
      Sort Key: table_b.column_y
      Sort Method: external merge  Disk: 14232kB
      Worker 0:  Sort Method: external merge  Disk: 4896kB
      Worker 1:  Sort Method: external merge  Disk: 6160kB
      ->  Parallel Append  (cost=0.00..5873.99 rows=116082 width=368) (actual time=0.009..8.534 rows=92865 loops=3)
            ->  Parallel Seq Scan on table_b_1 table_b_1  (cost=0.00..5163.05 rows=113444 width=368) (actual time=0.006..5.528 rows=90755 loops=3)
                  Filter: (user_id = 1)
            ->  ...
Planning Time: 1.946 ms
Execution Time: 77.598 ms

Здесь же памяти используется в разы меньше и хватает увеличения размера work_mem до 30 Mb.

set work_mem='30MB';
Gather Merge  (cost=36081.79..63169.48 rows=232164 width=368) (actual time=29.750..63.125 rows=278594 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Sort  (cost=35081.77..35371.97 rows=116082 width=368) (actual time=17.767..20.840 rows=92865 loops=3)
      Sort Key: table_b.column_y
      Sort Method: quicksort  Memory: 26359kB
      Worker 0:  Sort Method: quicksort  Memory: 12892kB
      Worker 1:  Sort Method: quicksort  Memory: 13296kB
      ->  Parallel Append  (cost=0.00..5873.99 rows=116082 width=368) (actual time=0.007..8.641 rows=92865 loops=3)
            ->  Parallel Seq Scan on table_b_1 table_b_1  (cost=0.00..5163.05 rows=113444 width=368) (actual time=0.005..5.642 rows=90755 loops=3)
                  Filter: (user_id = 45)
            ->  ...
Planning Time: 2.012 ms
Execution Time: 69.087 ms

Такой размер work_mem устанавливать относительно безопасно, так как суммарный размер используемой СУБД оперативной памяти вряд ли получится слишком большим.

Следующий пример запрос с использованием join:

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f JOIN schema_b.table_b b ON b.id = f.bpo_id WHERE column_x > 100;

Hash Join  (cost=10158.57..395715.04 rows=1275846 width=383) (actual time=17951.355..18798.673 rows=41 loops=1)
Hash Cond: (f.bpo_id = b.id)
->  Append  (cost=0.00..263263.32 rows=1275846 width=351) (actual time=17724.494..18673.100 rows=41 loops=1)
      ->  Seq Scan on table_a_1 f_1  (cost=0.00..12.62 rows=70 width=351) (actual time=0.038..0.038 rows=0 loops=1)
            Filter: (column_x > 100)
      ->  ...
->  Hash  (cost=5000.92..5000.92 rows=266692 width=32) (actual time=79.247..79.249 rows=267643 loops=1)
      Buckets: 65536  Batches: 8  Memory Usage: 2618kB
      ->  Seq Scan on table_b b  (cost=0.00..5000.92 rows=266692 width=32) (actual time=0.017..18.741 rows=267643 loops=1)
Planning Time: 1617.770 ms
JIT:
Functions: 2931
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1943.363 ms, Inlining 0.000 ms, Optimization 1580.248 ms, Emission 15330.172 ms, Total 18853.783 ms
Execution Time: 20788.785 ms

По плану видим, что используется hash join.

Строка «Buckets: 65536 Batches: 8 Memory Usage: 2618kB»

говорит нам о том, что в хеш-таблице, использованной для соединения, было 65536 корзин (Buckets), но на самом деле этих корзин больше: все они не поместились в work_mem, так как было задействовано 8 (Batches) пакетов, которые записываютя во временные файлы.
Увеличим work_mem c 4 до 30Мб и повторим — запустим команду EXPLAIN еще раз:

SET work_mem = '30MB';

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f JOIN schema_b.table_b b ON b.id = f.bpo_id WHERE column_x > 100;

Hash Join  (cost=8334.57..274947.04 rows=1275846 width=383) (actual time=17890.816..18842.765 rows=41 loops=1)
Hash Cond: (f.bpo_id = b.id)
->  Append  (cost=0.00..263263.32 rows=1275846 width=351) (actual time=17800.680..18752.489 rows=41 loops=1)
      ->  Seq Scan on table_a_1 f_1  (cost=0.00..12.62 rows=70 width=351) (actual time=0.040..0.041 rows=0 loops=1)
            Filter: (column_x > 100)
      ->  ...
->  Hash  (cost=5000.92..5000.92 rows=266692 width=32) (actual time=87.282..87.283 rows=267643 loops=1)
      Buckets: 524288  Batches: 1  Memory Usage: 20824kB
      ->  Seq Scan on table_b b  (cost=0.00..5000.92 rows=266692 width=32) (actual time=0.023..18.929 rows=267643 loops=1)
Planning Time: 213.257 ms
JIT:
Functions: 2931
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1793.908 ms, Inlining 0.000 ms, Optimization 1586.443 ms, Emission 15452.539 ms, Total 18832.890 ms
Execution Time: 20682.344 ms

Теперь вся хеш-таблица из 524288 корзин уместилась в памяти (Batches: 1), но на общем времени выполнения запроса это особо не сказалось, так как стоимость соединения не такая большая относительно стоимости других узлов запроса. В случае если стоимость соединения будет преобладать над стоимостью остальных узлов запроса, увеличение размера work_mem может сильно поспособствовать улучшению производительности.

Следующий пример запроса:

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a WHERE column_x > 100;
Gather  (cost=1000.00..1867776.17 rows=109335 width=675) (actual time=8523.234..17485.661 rows=7965 loops=1)
Workers Planned: 4
Workers Launched: 4
->  Parallel Append  (cost=0.00..1855842.67 rows=27345 width=675) (actual time=1703.379..10913.290 rows=1593 loops=5)
      ->  Parallel Seq Scan on table_a_month_2024_09 table_a_35  (cost=0.00..146392.54 rows=2587 width=675) (actual time=0.386..791.368 rows=707 loops=1)
            Filter: (column_x > 100)
            Rows Removed by Filter: 2992662
      ->  ...
Planning Time: 61.016 ms
Execution Time: 17488.673 ms

В этом запросе все упирается в медленное последовательное сканирование. Нам нужно просматривать всю таблицу и отсеивать неподходящие строки. Если подобные запросы используются часто и их производительность критична, можно добавить индекс по столбцу column_x. Но добавление индекса — это всегда палка о двух концах. С одной стороны мы получаем прирост производительности при чтении данных (и то не всегда), с другой стороны — используется дополнительная память для хранения индекса и снижается производительность запросов на вставку новых записей в таблицу. В случаях когда наиболее часто обращаются только к части значений в столбце, можно добавить частичный индекс. Он ускорит часть запросов использующих индекс, при этом не будет сильной просадки в других запросах, так как проиндексирована только часть таблицы и индекс не всегда требуется обновлять, также память, занимаемая частичным индексом, меньше, чем память, занимаемая полным индексом.

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f WHERE f.date BETWEEN '2024-01-01' AND '2024-06-01';
Gather  (cost=1000.00..678031.78 rows=547514 width=675) (actual time=573.888..641.552 rows=552951 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on table_a f  (cost=0.00..622280.38 rows=228131 width=675) (actual time=557.001..596.104 rows=184317 loops=3)
      Filter: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
      Rows Removed by Filter: 4672236
Planning Time: 0.260 ms
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.599 ms, Inlining 117.227 ms, Optimization 14.718 ms, Emission 15.567 ms, Total 148.111 ms
Execution Time: 658.582 ms

Следующий пример запроса:

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f WHERE f.date BETWEEN '2024-01-01' AND '2024-06-01';
Gather  (cost=1000.00..678031.78 rows=547514 width=675) (actual time=573.888..641.552 rows=552951 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on table_a f  (cost=0.00..622280.38 rows=228131 width=675) (actual time=557.001..596.104 rows=184317 loops=3)
      Filter: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
      Rows Removed by Filter: 4672236
Planning Time: 0.260 ms
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.599 ms, Inlining 117.227 ms, Optimization 14.718 ms, Emission 15.567 ms, Total 148.111 ms
Execution Time: 658.582 ms

Здесь мы сканируем таблицу и фильтруем ее по полю date, на котором есть индекс, но планировщик решил индекс не использовать, а предпочел считать всю таблицу, отбрасывая неподходящие по условию строки.

Отключим последовательное сканирование и вновь запустим explain:

SET enable_seqscan = OFF;

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f WHERE f.date BETWEEN '2024-01-01' AND '2024-06-01';

Gather  (cost=8472.45..697646.91 rows=547514 width=675) (actual time=31.362..128.172 rows=552951 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Bitmap Heap Scan on table_a f  (cost=7472.45..641895.51 rows=228131 width=675) (actual time=16.629..87.796 rows=184317 loops=3)
      Recheck Cond: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
      Heap Blocks: exact=8846
      ->  Bitmap Index Scan on table_a_date_index  (cost=0.00..7335.58 rows=547514 width=0) (actual time=27.439..27.440 rows=552951 loops=1)
            Index Cond: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
Planning Time: 0.265 ms
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.626 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.626 ms
Execution Time: 145.257 ms

При индексном сканировании фактическое время выполнения запроса оказалось более чем в 4 раза меньше. Планировщик выбрал неоптимальный план, потому что считает, что последовательное сканирование отработает в этом случае быстрее, чем индексное. Параметры seq_page_cost и random_page_cost имеют значения, по умолчанию равные 1 и 4. Такие значения справедливы для жестких дисков, где есть движущаяся считывающая головка, из-за которой произвольное чтение с диска работает медленно. В твердотельных накопителях такой сильной разницы нет и значения по умолчанию для параметра random_page_cost рекомендуют изменить. Можно поэкспериментировать с запросами, чтобы понять, какое реальное соотношение последовательного и произвольного чтения на вашем устройстве. Включим последовательное сканирование, установим значение random_page_cost равное 1.1 и выполним запрос снова.

SET enable_seqscan = ON;

SET random_page_cost = 1.1;

EXPLAIN ANALYZE SELECT * FROM schema_a.table_a f WHERE f.date BETWEEN '2024-01-01' AND '2024-06-01';

Gather  (cost=8472.45..697646.91 rows=547514 width=675) (actual time=31.362..128.172 rows=552951 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Bitmap Heap Scan on table_a f  (cost=7472.45..641895.51 rows=228131 width=675) (actual time=16.629..87.796 rows=184317 loops=3)
      Recheck Cond: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
      Heap Blocks: exact=8846
      ->  Bitmap Index Scan on table_a_date_index  (cost=0.00..7335.58 rows=547514 width=0) (actual time=27.439..27.440 rows=552951 loops=1)
            Index Cond: ((date >= '2024-01-01'::date) AND (date <= '2024-06-01'::date))
Planning Time: 0.265 ms
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.626 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.626 ms
Execution Time: 145.257 ms

Планировщик предпочел индексное сканирование.

Визуализаторы плана запроса

Иногда план запроса получается слишком длинным и в нем трудно найти проблемные узлы. Для облегчения этой задачи существуют визуализаторы плана запроса, которые подсвечивают самые тяжелые узлы.

Есть визуализаторы, которые дают советы по повышению производительности запроса, например https://explain.tensor.ru/plan/

Поиск «тяжелых» запросов

В Postgres имеется модуль pg_stat_statements. Если у нас система в целом работает медленно, но непонятно, какие именно запросы оптимизировать в первую очередь, этот модуль нам поможет. Чтобы включить его, необходимо добавить pg_stat_statements в shared_preload_libraries в файле postgresql.conf. После включения модуля начнется сбор статистики выполненных запросов. Просмотреть эту информацию можно через представление pg_stat_statements. При помощи следующего запроса мы можем получить список всех запросов, отсортированных по времени их выполнения от самых тяжелых к самым легким.

SELECT query, calls,
    round(total_exec_time::numeric, 2) AS total_time,
    round(mean_exec_time::numeric, 2) AS mean_time,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage
FROM  pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Query	Calls	Total Time	Mean Time	Percentage
SELECT query, calls. round (total_exec time: numeric. $1) AS total ti	333	37 533.86	112.71	23.95
SELECT «general». «table_a». «id», «general». «table_b»,	2	31 888.83	15 944.41	20.35

Заключение

В данной статье мы рассмотрели ключевые аспекты использования команды EXPLAIN в PostgreSQL для анализа и оптимизации выполнения SQL-запросов. Понимание работы планировщика запросов и различных методов сканирования данных, таких как последовательное, индексное и битовое сканирование, является важным шагом к повышению производительности базы данных.

Мы узнали, что EXPLAIN позволяет получить информацию о том, как PostgreSQL будет выполнять запрос, включая стоимость выполнения каждой операции и реальные временные метрики при использовании опции ANALYZE. Это дает возможность выявлять узкие места в производительности и принимать обоснованные решения о необходимости создания индексов, изменения параметров конфигурации и оптимизации самих запросов.

В заключение: анализ и оптимизация запросов с использованием EXPLAIN и других инструментов PostgreSQL позволяет не только улучшить производительность текущих запросов, но и обеспечить стабильную работу системы в будущем, что особенно важно для приложений с высоким уровнем нагрузки и большими объемами данных.