Версия: 1.0.0

Github

pg_stat_advisor — это расширение Tantor SE, предназначенное для анализа производительности запросов и создания дополнительных статистик для улучшения плана запроса.

Для создания расширенной статистики должны быть выполнены следующие условия: 1. max_parallel_workers_per_gather должен быть установлен в 0 (параллельные рабочие процессы отключены). 2. SQL-запрос не должен быть UPDATE или DELETE. 3. План выполнения не может включать узлы NestedLoop, MergeJoin или HashJoin. 4. Запрос должен выполняться по нетемпоральной таблице. 5. Условие WHERE должно фильтровать от 2 до 8 столбцов (включительно) из одной таблицы. 6. Таблица была проанализирована, и по крайней мере один столбец имеет ndistinct != 1. 7. Расширенная статистика не будет создана, если эти столбцы уже образуют многоколонный индекс. 8. Расширенная статистика не должна уже существовать для этих столбцов в таблице.

Tantor SE 17 или выше

Чтобы собрать и установить расширение, выполните «make», а затем «sudo make install».

make
sudo make install

Если это расширение скомпилировано с инфраструктурой PGXS, то инструмент pg_config должен быть доступен из вашей переменной окружения PATH. А затем выполните “USE_PGXS=1 make, и затем”sudo USE_PGXS=1 make install".

USE_PGXS=1 make
sudo USE_PGXS=1 make install

Добавьте pg_stat_advisor в параметр конфигурации shared_preload_libraries в вашем файле postgresql.conf, затем перезапустите базу данных Tantor SE, чтобы применить изменения.

shared_preload_libraries = 'pg_stat_advisor';

Параметр pg_stat_advisor.version отображает текущую версию расширения; он доступен только для чтения и предназначен для информационных целей.

SHOW pg_stat_advisor.version;

Существует GUC pg_stat_advisor.suggest_statistics_threshold, который может быть использован для установки suggest_statistics_threshold. Это отношение планируемых строк к общему количеству произведенных кортежей. Если параметр установлен на 0, вывод отключается.

Например:

SET pg_stat_advisor.suggest_statistics_threshold = 0.1;

Параметр pg_stat_advisor.min_duration GUC устанавливает минимальную продолжительность запроса (в миллисекундах), необходимую для активации расширения; установка его в -1 полностью отключает расширение.

SET pg_stat_advisor.min_duration = 100;

Параметр pg_stat_advisor.ring_buffer_capacity задает размер (в байтах) кольцевого буфера, используемого для хранения данных о расширенной статистике, которая будет создана; этот буфер работает циклически, перезаписывая старые записи при заполнении. Он может быть установлен только в postgresql.conf.

pg_stat_advisor.ring_buffer_capacity = 1024

Чтобы установить максимальное количество рабочих процессов, которые будут генерировать и собирать расширенную статистику, отрегулируйте параметр pg_stat_advisor.deferred_jobs_max в postgresql.conf.

SET pg_stat_advisor.pg_stat_advisor.deferred_jobs_max = 1024;

Чтобы собрать статистику сразу после создания расширенной статистики, включите параметр pg_stat_advisor.enable_analyze. Чтобы отложить сбор и позволить autovacuum или ручному ANALYZE обработать это, отключите этот параметр.

SET pg_stat_advisor.enable_analyze = on;

Чтобы ограничить количество столбцов таблицы, используемых при создании расширенной статистики, настройте параметр pg_stat_advisor.ext_stats_max_columns.

SET pg_stat_advisor.ext_stats_max_columns = 8;

SET pg_stat_advisor.suggest_statistics_threshold = 0.1;

CREATE TABLE t (i INT, j INT);
INSERT INTO t SELECT i/10, i/100 FROM generate_series(1, 1000000) i;
ANALYZE t;
EXPLAIN ANALYZE SELECT * FROM t WHERE i = 100 AND j = 10;
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------
------------------
 Gather  (cost=1000.00..11675.10 rows=1 width=8) (actual time=0.526..61.564 rows=10 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t  (cost=0.00..10675.00 rows=1 width=8) (actual time=35.369..54.44
7 rows=3 loops=3)
         Filter: ((i = 100) AND (j = 10))
         Rows Removed by Filter: 333330
 Planning Time: 0.148 ms
 Execution Time: 61.589 ms
(8 rows)


EXPLAIN ANALYZE SELECT * FROM t WHERE i = 100 AND j = 10;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------
------------------
 Gather  (cost=1000.00..11675.10 rows=10 width=8) (actual time=0.400..59.292 rows=10 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t  (cost=0.00..10675.00 rows=1 width=8) (actual time=35.614..54.29
1 rows=3 loops=3)
         Filter: ((i = 100) AND (j = 10))
         Rows Removed by Filter: 333330
 Planning Time: 0.081 ms
 Execution Time: 59.413 ms
(8 rows)

Илья Евдокимов, Эдуард Степанов