G.9. pgpro_temp_stats — сбор статистики по нескольким столбцам временных таблиц #
G.9.1. Описание #
Расширение pgpro_temp_stats собирает расширенную статистику по нескольким столбцам временных таблиц. Оно работает автоматически во время выполнения запросов, которые ссылаются на временные таблицы.
Собранные данные позволяют планировщику точнее предсказывать количество строк для таких запросов и сокращать их время выполнения.
G.9.2. Установка #
Расширение pgpro_temp_stats поставляется вместе с Postgres Pro Enterprise в виде отдельного пакета pgpro-temp-stats-ent-17 (подробные инструкции по установке приведены в Главе 17).
Чтобы включить pgpro_temp_stats, выполните следующие шаги:
Загрузите расширение одним из следующих способов:
Чтобы загрузить его в текущем сеансе, используйте команду LOAD.
LOAD 'pgpro_temp_stats';
Чтобы загрузить его глобально для всех сеансов, добавьте имя библиотеки в переменную shared_preload_libraries в файле
postgresql.conf.shared_preload_libraries = 'pgpro_temp_stats'
Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Чтобы убедиться, что библиотека pgpro_temp_stats установлена правильно, можно выполнить следующую команду:
SHOW shared_preload_libraries;
Создайте расширение pgpro_temp_stats, выполнив следующий запрос:
CREATE EXTENSION pgpro_temp_stats;
Включите расширение, установив для параметра pgpro_temp_stats.enable значение
on.SET pgpro_temp_stats.enable = 'on';
Также рекомендуется включить системный каталог в оперативной памяти для временных объектов с помощью параметра конфигурации enable_temp_memory_catalog. За подробной информацией обратитесь к разделу Хранение собранной статистики.
enable_temp_memory_catalog = on
G.9.3. Использование #
Расширение pgpro_temp_stats автоматически ищет запросы, которые ссылаются на временные таблицы, и собирает отсутствующие статистические данные во время выполнения этих запросов.
pgpro_temp_stats собирает статистику следующих типов:
статистика подсчёта числа различных значений;
статистика определения функциональных зависимостей;
статистика списков самых часто встречаемых значений.
pgpro_temp_stats обрабатывает временные таблицы в следующих предложениях:
WHEREJOINGROUP BYORDER BY
G.9.3.1. Базовое использование #
Если отсутствует расширенная статистика для столбцов временной таблицы, pgpro_temp_stats выполняет следующие действия:
Выполняет команду CREATE STATISTICS для столбцов, задействованных в запросе.
Эта команда создаёт объект расширенной статистики с именем, которое имеет следующий формат:
stN_идентификатор_базы_данных_идентификатор_отношения.Где:
N— порядковый номер объекта статистики.идентификатор_базы_данных— идентификатор базы данных, в которой выполняется запрос.идентификатор_отношения— идентификатор временной таблицы, для которой собирается статистика.
Этот шаг выполняется, только если запрос задействует два или более столбцов одной и той же временной таблицы.
Выполняет команду ANALYZE для столбцов, задействованных в запросе, и для столбцов существующих объектов статистики для той же временной таблицы.
Например, для запроса, который ссылается на два столбца одной временной таблицы, действия pgpro_temp_stats выглядят следующим образом:
SELECT * FROM t1 WHERE (a = 1) AND (b = 0); DEBUG: [TS] CREATE STATISTICS st0_5_16389 ON a, b FROM t1; DEBUG: [TS] ANALYZE t1 (a, b)
Расширение pgpro_temp_stats записывает сообщения о его работе в журнал на уровне важности DEBUG1.
G.9.3.2. Использование для соединённых таблиц #
pgpro_temp_stats может собирать статистику для запросов, которые соединяют две и более временных таблиц или временные таблицы с постоянными таблицами. В этих случаях pgpro_temp_stats собирает статистику по столбцам временных таблиц, которые используются в условиях соединения.
SELECT * FROM t2 JOIN t3 ON t2.id = t3.id AND t2.val = t3.val; DEBUG: [TS] CREATE STATISTICS st0_5_16385 ON id, val from t2; DEBUG: [TS] ANALYZE t2 (id, val); DEBUG: [TS] CREATE STATISTICS st0_5_16386 id, val from t3; DEBUG: [TS] ANALYZE t3 (id, val);
G.9.3.3. Использование с глобальной командой ANALYZE #
Пока расширение pgpro_temp_stats включено, игнорируются все глобальные команды ANALYZE, которые запущены по целым временным таблицам автоматически или вручную.
Если необходимо выполнить глобальные команды ANALYZE, временно отключите расширение с помощью параметра pgpro_temp_stats.enable и потом после завершения необходимых команд обратно включите расширение.
G.9.3.4. Хранение собранной статистики #
Когда для параметра enable_temp_memory_catalog установлено значение off (по умолчанию), pgpro_temp_stats хранит собранные статистические данные в обычных системных каталогах.
Созданные объекты расширенной статистики добавляются в системный каталог pg_statistic_ext, как и другие объекты статистики. Статистические данные, созданные командами ANALYZE, добавляются в системный каталог pg_statistic.
Однако использование обычных системных каталогов может быть узким местом для pgpro_temp_stats. Чтобы ускорить работу расширения, используйте системный каталог в оперативной памяти для временных объектов. Чтобы включить этот каталог, установите для параметра enable_temp_memory_catalog значение on.
В этом случае для доступа к статистическим данным pgpro_temp_stats используйте представления temp_pg_class, temp_pg_attribute, temp_pg_statistic_ext и temp_pg_stats.
G.9.4. Пример #
В этом примере показано, как работает расширение pgpro_temp_stats.
-- Расширение pgpro_temp_stats отключено
-- Создайте временную таблицу и вставьте в неё данные
CREATE TEMP TABLE t1 (x int, y int);
INSERT INTO t1 (x, y)
SELECT val.x, val.y
FROM (VALUES
(3, 30, 100000),
(4, 40, 10000),
(5, 50, 100),
(6, 60, 100),
(7, 70, 10)
) AS val(x, y, cnt)
CROSS JOIN LATERAL generate_series(1, val.cnt);
-- Выполните ANALYZE для всей таблицы
ANALYZE t1;
Time: 33.119 ms
-- Выполните запрос, задействующий столбцы созданной временной таблицы
-- Оценка количества строк гораздо ниже фактического числа строк
EXPLAIN (ANALYZE, COSTS, VERBOSE)
SELECT t1.x, t1.y, t2.y, t3.y
FROM t1
JOIN t1 as t2 ON t1.x = t2.x
JOIN t1 as t3 ON t1.x = t3.x
WHERE t1.x BETWEEN 4 AND 6
AND t1.y >= 50
AND t2.x BETWEEN 4 AND 6
AND t2.y >= 50
AND t3.x BETWEEN 4 AND 6
AND t3.y >= 50;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..7409.53 rows=8377 width=16) (actual time=27.913..546.396 rows=2000000 loops=1)
Output: t1.x, t1.y, t2.y, t3.y
Join Filter: (t1.x = t3.x)
Rows Removed by Join Filter: 2000000
-> Nested Loop (cost=0.00..4841.34 rows=439 width=16) (actual time=17.991..25.575 rows=20000 loops=1)
Output: t1.x, t1.y, t2.y, t2.x
Join Filter: (t1.x = t2.x)
Rows Removed by Join Filter: 20000
-> Seq Scan on pg_temp.t1 (cost=0.00..2416.68 rows=23 width=8) (actual time=9.183..9.806 rows=200 loops=1)
Output: t1.x, t1.y
Filter: ((t1.x >= 4) AND (t1.x <= 6) AND (t1.y >= 50))
Rows Removed by Filter: 110010
-> Materialize (cost=0.00..2416.79 rows=23 width=8) (actual time=0.044..0.053 rows=200 loops=200)
Output: t2.y, t2.x
-> Seq Scan on pg_temp.t1 t2 (cost=0.00..2416.68 rows=23 width=8) (actual time=8.777..8.818 rows=200 loops=1)
Output: t2.y, t2.x
Filter: ((t2.x >= 4) AND (t2.x <= 6) AND (t2.y >= 50))
Rows Removed by Filter: 110010
-> Materialize (cost=0.00..2416.79 rows=23 width=8) (actual time=0.001..0.008 rows=200 loops=20000)
Output: t3.y, t3.x
-> Seq Scan on pg_temp.t1 t3 (cost=0.00..2416.68 rows=23 width=8) (actual time=9.903..9.932 rows=200 loops=1)
Output: t3.y, t3.x
Filter: ((t3.x >= 4) AND (t3.x <= 6) AND (t3.y >= 50))
Rows Removed by Filter: 110010
Planning Time: 0.341 ms
Execution Time: 605.590 ms
(26 rows)
-- Включите расширение pgpro_temp_stats
SET pgpro_temp_stats.enable = on;
-- Настройте сообщения, чтобы видеть информацию о создании статистики и анализе
SET client_min_messages = 'debug1';
-- Выполните тот же запрос. Для столбцов, на которые ссылается этот запрос, pgpro_temp_stats
-- автоматически создаёт объект расширенной статистики и запускает ANALYZE
EXPLAIN (ANALYZE, COSTS, VERBOSE)
SELECT t1.x, t1.y, t2.y, t3.y
FROM t1
JOIN t1 as t2 ON t1.x = t2.x
JOIN t1 as t3 ON t1.x = t3.x
WHERE t1.x BETWEEN 4 AND 6
AND t1.y >= 50
AND t2.x BETWEEN 4 AND 6
AND t2.y >= 50
AND t3.x BETWEEN 4 AND 6
AND t3.y >= 50;
DEBUG: [TS] CREATE STATISTICS st0_5_16651 ON x, y FROM t1;
DEBUG: [TS] ANALYZE t1 (x, y)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=4837.85..54214.44 rows=4050599 width=16) (actual time=28.505..235.628 rows=2000000 loops=1)
Output: t1.x, t1.y, t2.y, t3.y
Hash Cond: (t1.x = t3.x)
-> Hash Join (cost=2418.93..5146.57 rows=27002 width=16) (actual time=19.438..22.972 rows=20000 loops=1)
Output: t1.x, t1.y, t2.y, t2.x
Hash Cond: (t1.x = t2.x)
-> Seq Scan on pg_temp.t1 (cost=0.00..2416.68 rows=180 width=8) (actual time=10.214..10.516 rows=200 loops=1)
Output: t1.x, t1.y
Filter: ((t1.x >= 4) AND (t1.x <= 6) AND (t1.y >= 50))
Rows Removed by Filter: 110010
-> Hash (cost=2416.68..2416.68 rows=180 width=8) (actual time=9.184..9.186 rows=200 loops=1)
Output: t2.y, t2.x
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on pg_temp.t1 t2 (cost=0.00..2416.68 rows=180 width=8) (actual time=9.114..9.146 rows=200 loops=1)
Output: t2.y, t2.x
Filter: ((t2.x >= 4) AND (t2.x <= 6) AND (t2.y >= 50))
Rows Removed by Filter: 110010
-> Hash (cost=2416.68..2416.68 rows=180 width=8) (actual time=9.056..9.057 rows=200 loops=1)
Output: t3.y, t3.x
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on pg_temp.t1 t3 (cost=0.00..2416.68 rows=180 width=8) (actual time=8.977..9.014 rows=200 loops=1)
Output: t3.y, t3.x
Filter: ((t3.x >= 4) AND (t3.x <= 6) AND (t3.y >= 50))
Rows Removed by Filter: 110010
Planning Time: 39.366 ms
Execution Time: 292.291 ms
(26 rows)
-- В результате оценка количества строк становится точнее
-- Время выполнения запроса сокращаетсяG.9.5. Параметры конфигурации #
pgpro_temp_stats.enable(boolean) #Включает или отключает расширение pgpro_temp_stats. Значение по умолчанию —
off. Изменить этот параметр могут только суперпользователи.pgpro_temp_stats.ts_duration(boolean) #Указывает, показывать ли продолжительность работы pgpro_temp_stats, в милисекундах. Значение по умолчанию —
off.
G.9.6. Функции #
-
temp_memory_catalog_dump() returns jsonb# Возвращает всё содержимое системного каталога в оперативной памяти для временных объектов в виде одного значения
jsonb. Верхнеуровневые ключи представляют собой OID таблиц каталога. Каждое значение — объект с массивомtuples, содержащим один элемент на хранимый кортеж.Это функция используется на внутреннем уровне представлениями pgpro_temp_stats, описанными ниже. Можно вручную выполнять эту функцию для целей отладки.
G.9.7. Представления #
G.9.7.1. Представление temp_pg_class #
Представление temp_pg_class содержит подробную информацию о временных таблицах в системном каталоге в оперативной памяти. Столбцы представления показаны в Таблице G.98.
Таблица G.98. Столбцы temp_pg_class
| Имя | Тип | Описание |
|---|---|---|
oid | oid | OID отношения |
relname | name | Имя отношения |
relnamespace | oid | OID пространства имён, которое содержит это отношение |
relowner | oid | OID владельца отношения |
relpersistence | char | Тип хранения отношения, всегда t, означая временную таблицу |
G.9.7.2. Представление temp_pg_attribute #
Представление temp_pg_attribute содержит подробную информацию о столбцах временных таблиц в системном каталоге в оперативной памяти. Столбцы представления показаны в Таблице G.99.
Таблица G.99. Столбцы temp_pg_attribute
| Имя | Тип | Описание |
|---|---|---|
attrelid | oid | OID отношения, к которому принадлежит столбец |
attnum | int2 | Номер столбца |
attname | name | Имя столбца |
attisdropped | bool | TRUE, если столбец был удалён и больше не является действительным |
G.9.7.3. Представление temp_pg_statistic_ext #
Представление temp_pg_statistic_ext содержит подробную информацию об объектах расширенной статистики в системном каталоге в оперативной памяти. Столбцы представления показаны в Таблице G.100.
Таблица G.100. Столбцы temp_pg_statistic_ext
| Имя | Тип | Описание |
|---|---|---|
stxname | name | Имя объекта статистики |
stxkind | char[] | Массив, содержащий коды для включённых типов статистики. Допустимые значения: d для статистики по количеству различных значений (n-distinct), f для статистики по функциональным зависимостям (functional dependency) и m для списков самых часто встречаемых значений (MCV) |
stxexprs | text | Деревья выражения для статистики по выражениям. NULL, если все атрибуты объекта статистики являются простыми ссылками на столбцы |
G.9.7.4. Представление temp_pg_stats #
Представление temp_pg_stats содержит подробную информацию об объектах статистики по столбцам в системном каталоге в оперативной памяти. Столбцы представления показаны в Таблице G.101.
Таблица G.101. Столбцы temp_pg_stats
| Имя | Тип | Описание |
|---|---|---|
tablename | name | Имя временной таблицы (из temp_pg_class) |
attname | name | Имя столбца (из temp_pg_attribute) |