G.5. pgpro_planner — дополнительные преобразования и оптимизации запросов #
G.5.1. Описание #
Расширение pgpro_planner предоставляет преобразования и оптимизации запросов, которые позволяют создавать эффективные планы выполнения запросов и улучшать производительность.
Преобразования представлены ниже:
упрощение операций
x + 0,x - 0,x * 1,x / 1иx * 0доxкеширование результатов связанных подзапросов с помощью узлов
Memoize
По умолчанию это расширение отключено, чтобы избежать неявных или неожиданных изменений в планах запросов. Его можно включить, как описано ниже. Каждое преобразование также можно включить или отключить отдельно с помощью соответствующих параметров конфигурации для соответствия определённым требованиям пользователей.
G.5.2. Установка #
Расширение pgpro_planner поставляется вместе с Postgres Pro Enterprise в виде отдельного пакета pgpro-planner-ent-18 (подробная инструкция по установке приведена в Главе 17).
Чтобы включить pgpro_planner, выполните следующие действия:
Загрузите расширение одним из следующих способов:
глобально для всех сеансов путём добавления имени библиотеки в переменную shared_preload_libraries в файле
postgresql.confshared_preload_libraries = 'pgpro_planner'
в текущем сеансе с помощью команды LOAD без добавления имени библиотеки в переменную
shared_preload_librariesLOAD 'pgpro_planner';
Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Чтобы убедиться, что библиотека pgpro_planner установлена правильно, можно выполнить следующую команду:
SHOW shared_preload_libraries;
Включите расширение, установив для параметра pgpro_planner.enable значение
on.SET pgpro_planner.enable = 'on';
G.5.3. Упрощение тривиальных арифметических операций #
Даже для тривиальных арифметических операций, таких как x + 0, стандартный планировщик предсказывает количество строк и избирательность, как для выражений, и не может использовать индексы в планах выполнения запросов.
Расширение pgpro_planner может упрощать и переписывать следующие базовые арифметические операции:
x + 0→xx - 0→xx * 1→xx / 1→xx * 0→x(только еслиxнеNULL)
Эти упрощения позволяют планировщику не только избегать ненужных вычислений, но и сокращать ошибки в оценках, создавать планы с индексами и эффективными методами доступа, таким образом значительно улучшая производительность.
Это упрощение можно отключить, установив для параметра pgpro_planner.enable_simplify_trivials значение off, например, в целях отладки.
G.5.3.1. Пример арифметического упрощения #
Этот пример демонстрирует, как арифметическое упрощение pgpro_planner затрагивает планы выполнения для следующего запроса:
SELECT * FROM t WHERE x + 0 > 900;
Для исходной арифметической операции планировщик создаёт план выполнения с узлом Seq Scan и не использует индекс.
Seq Scan on t (cost=0.00..20.00 rows=333 width=4) (actual time=0.500..0.571 rows=100.00 loops=1) Filter: ((x + 0) > 900) Rows Removed by Filter: 900 Buffers: shared hit=5 Planning: Buffers: shared hit=14 Planning Time: 0.432 ms Execution Time: 0.627 ms (8 rows)
Когда pgpro_planner упрощает такую операцию, планировщик создаёт план с узлом Index Only Scan и выполняет сравнения значений в узле Index Cond, что помогает сократить время выполнения запроса.
Index Only Scan using idx_t_x on t (cost=0.28..10.03 rows=100 width=4) (actual time=0.077..0.247 rows=100.00 loops=1) Index Cond: (x > 900) Heap Fetches: 100 Index Searches: 1 Buffers: shared hit=4 Planning Time: 0.217 ms Execution Time: 0.319 ms (7 rows)
G.5.3.2. Ограничения арифметического упрощения #
pgpro_planner имеет следующие ограничения для арифметического упрощения:
Поддерживаются только следующие операции:
x + 0,x - 0,x * 1,x / 1иx * 0(еслиxнеNULL).Поддерживаются только следующие типы данных для констант:
int2,int4,floatиnumeric.Не могут быть упрощены операции, которые используют сложные выражения для получения
0или1, напримерx - 100 - 100.Не могут быть упрощены операции, которые используют изменяемые функции.
G.5.4. Кеширование связанных подзапросов #
Связанные подзапросы используют значения из внешних запросов, например, через предложение WHERE. Эти подзапросы выполняются один раз для каждой строки внешнего запроса, поэтому они могут сильно влиять на производительность.
Расширение pgpro_planner может кешировать результаты параметризованных связанных подзапросов с помощью добавления узлов Memoize в планы выполнения запросов. Это позволяет пропускать идентичные сканирования для одних и тех же параметров и улучшать производительность.
Эта функциональность включена, если для глобального параметра конфигурации enable_memoize и для параметра pgpro_planner.memoize_subplan расширения pgpro_planner установлены значения on (по умолчанию).
pgpro_planner обрабатывает запрос во время стадии создания плана и выполняет следующие шаги:
pgpro_planner выполняет поиск поддерживаемых связанных подзапросов.
Если для параметра pgpro_planner.memoize_check_uniqueness установлено значение
on(по умолчанию), pgpro_planner проверяет уникальность для значений столбцов, задействованных в подзапросах, на основе статистической информации. Это помогает определить, полезно ли использоватьMemoize.Узел
Memoizeможет быть добавлен в следующих случаях:Не существует уникального индекса по столбцу, а значения столбца не уникальны на основе статистической информации.
Существует уникальный индекс по столбцу, но значения столбца содержат значения
NULLна основе статистической информации.Существует составной уникальный индекс, но значения задействованного столбца не уникальны на основе статистической информации.
Статистическая информация для столбца ещё не собрана независимо от того, существует ли индекс.
pgpro_planner оценивает ожидаемую стоимость результирующего плана с узлом
Memoize.Если ожидаемая стоимость меньше, чем стоимость изначального плана, pgpro_planner добавляет узел
Memoizeв начало соответствующего подплана.
Кеширование связанных подзапросов можно отключить, установив для параметра pgpro_planner.memoize_subplan значение off, например, если связанные подзапросы редко используются. Чтобы отключить проверку уникальности в целях отладки, установите для параметра pgpro_planner.memoize_check_uniqueness значение off.
G.5.4.1. Пример кеширования подзапросов #
Этот пример демонстрирует, как работает кеширование связанных подзапросов в разных сценариях.
Создайте две простых таблицы с именами t и t1, а также уникальный индекс по столбцу x первой таблицы.
CREATE TABLE t (x int, y int); INSERT INTO t SELECT id, id%20 FROM generate_series(1,1000) id; CREATE UNIQUE INDEX t_x_idx ON t (x); CREATE TABLE t1 (x1 int); INSERT INTO t1 SELECT id%2 FROM generate_series(1,1000) id;
Запустите следующий запрос, в котором подзапрос использует значения из связанного столбца с именем y. Уникального индекса по этому столбцу не существует, поэтому узел Memoize добавлен в план выполнения запроса.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
QUERY PLAN
------------------------------------------------
Seq Scan on t
Filter: (SubPlan 1)
SubPlan 1
-> Memoize
Cache Key: t.y
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.y)
(10 rows)Запустите другой запрос, в котором подзапрос использует значения из связанного столбца с именем x. Уникальный индекс по этому столбцу существует, но статистическая информация ещё не собрана, поэтому узел Memoize генерируется.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
------------------------------------------------
Seq Scan on t
Filter: (SubPlan 1)
SubPlan 1
-> Memoize
Cache Key: t.x
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(10 rows)Запустите тот же запрос ещё раз. Статистическая информация для столбца x теперь собрана, и уникальный индекс всё ещё существует, поэтому узел Memoize не добавлен в план.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (x = (SubPlan 1))
SubPlan 1
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(7 rows)Удалите ранее созданный индекс и создайте новый составной индекс по обоим столбцам с именами x и y.
CREATE UNIQUE INDEX on t(x,y); DROP INDEX t_x_idx;
Запустите запрос, ссылающийся на связанный столбец y. Хотя существует составной уникальный индекс, значения этого столбца не уникальны на основе статистической информации. Узел Memoize добавляется в результирующий план.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
SubPlan 1
-> Memoize
Cache Key: t.y
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.y)
(10 rows)Запустите запрос, ссылающийся на связанный столбец x. Для этого случая узел Memoize по-прежнему не генерируется, поскольку значения столбца уникальны на основе статистической информации.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
SubPlan 1
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(7 rows)G.5.4.2. Ограничения кеширования подзапросов #
pgpro_planner не может кешировать результаты связанного подзапроса в следующих случаях:
Подзапрос использует изменяемые функции.
Подзапрос использует нестандартные операторы.
Подзапрос использует операнды с разными типами без совместимых классов операторов хеширования.
Подзапрос использует агрегатные функции
minилиmax.Дерево плана запроса не содержит соответствующий узел подплана.
Оценённая стоимость целевого плана запроса с узлами
Memoizeвыше, чем стоимость изначального плана.
G.5.5. Параметры конфигурации #
pgpro_planner.enable(boolean) #Включает или отключает расширение pgpro_planner. Значение по умолчанию —
off, чтобы избежать неявных или неожиданных изменений в планах запросов.pgpro_planner.enable_simplify_trivials(boolean) #Включает или отключает упрощение тривиальных арифметических операций. Значение по умолчанию —
on.pgpro_planner.memoize_subplan(boolean) #Включает или отключает кеширование связанных подзапросов. Значение по умолчанию —
on.pgpro_planner.memoize_check_uniqueness(boolean) #Включает или отключает умную логику для проверки уникальности значений столбцов для кеширования связанных подзапросов. Значение по умолчанию —
on.pgpro_planner.force_memoize_subplan(boolean) #Включает или отключает автоматическое добавление узла
Memoizeдля всех связанных подзапросов, игнорируя модель стоимости планов и проверку уникальности. Этот параметр предназначен только для целей отладки, не используйте его в производственной среде. Значение по умолчанию —off.
G.5. pgpro_planner — additional query transformations and optimizations #
G.5.1. Description #
The pgpro_planner extension provides query transformations and optimizations that allow producing efficient query execution plans and improving performance.
These optimizations are the following:
simplification of
x + 0,x - 0,x * 1,x / 1, andx * 0operations toxcaching results of correlated subqueries using the
Memoizenodes
By default, the extension is disabled to avoid implicit or unexpected query plan changes. You can enable it as described below. Each transformation can also be enabled or disabled separately using the corresponding configuration parameters to fit specific user needs.
G.5.2. Installation #
The pgpro_planner extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-planner-ent-18 (for the detailed installation instructions, see Chapter 17).
To enable pgpro_planner, do the following:
Load the extension in one of the following ways:
globally for all sessions by adding the library name to the shared_preload_libraries variable in the
postgresql.conffileshared_preload_libraries = 'pgpro_planner'
in the current session using the LOAD command without adding the library name to
shared_preload_librariesLOAD 'pgpro_planner';
Reload the database server for the changes to take effect.
To verify that the pgpro_planner library is installed correctly, you can run the following command:
SHOW shared_preload_libraries;
Enable the extension by setting the pgpro_planner.enable parameter to
on.SET pgpro_planner.enable = 'on';
G.5.3. Simplification of Trivial Arithmetic Operations #
Even for trivial arithmetic operations, such as x + 0, the standard planner predicts selectivity and cardinality as it does for expressions and cannot use indexes in query execution plans.
The pgpro_planner extension can simplify and rewrite the following basic arithmetic operations:
x + 0→xx - 0→xx * 1→xx / 1→xx * 0→x(only ifxis notNULL)
This simplification allows the planner not only to avoid unnecessary calculations, but also to reduce estimation errors, produce plans with indexes and effective access methods, and thus significantly improve performance.
You can disable this simplification by setting the pgpro_planner.enable_simplify_trivials parameter to off, for instance, for debugging purposes.
G.5.3.1. Arithmetic Simplification Example #
This example demonstrates how pgpro_planner arithmetic simplification affects execution plans for the following query:
SELECT * FROM t WHERE x + 0 > 900;
For the initial arithmetic operation, the planner produces the query execution plan with Seq Scan and does not use an index.
Seq Scan on t (cost=0.00..20.00 rows=333 width=4) (actual time=0.500..0.571 rows=100.00 loops=1) Filter: ((x + 0) > 900) Rows Removed by Filter: 900 Buffers: shared hit=5 Planning: Buffers: shared hit=14 Planning Time: 0.432 ms Execution Time: 0.627 ms (8 rows)
When pgpro_planner simplifies this operation, the planner creates the plan with Index Only Scan and performs value comparisons in Index Cond, which helps to reduce query execution time.
Index Only Scan using idx_t_x on t (cost=0.28..10.03 rows=100 width=4) (actual time=0.077..0.247 rows=100.00 loops=1) Index Cond: (x > 900) Heap Fetches: 100 Index Searches: 1 Buffers: shared hit=4 Planning Time: 0.217 ms Execution Time: 0.319 ms (7 rows)
G.5.3.2. Arithmetic Simplification Limitations #
pgpro_planner has the following limitations for arithmetic simplification:
Only the following operations are supported:
x + 0,x - 0,x * 1,x / 1, andx * 0(ifxis notNULL).Only the following data types for constants are supported:
int2,int4,float, andnumeric.Operations that use complex expressions to get
0or1, such asx - 100 - 100, cannot be simplified.Operations that use volatile functions cannot be simplified.
G.5.4. Caching of Correlated Subqueries #
Correlated subqueries use values from their outer queries, for instance, via the WHERE clause. These subqueries are executed once for each row of the outer query, so they can have major impact on performance.
The pgpro_planner extension can cache results of parameterized correlated subqueries by adding the Memoize nodes to query execution plans. This allows skipping identical scans for the same parameters and improving performance.
This functionality is enabled if the global enable_memoize configuration parameter and the pgpro_planner.memoize_subplan parameter of pgpro_planner are set to on (default).
pgpro_planner processes a query during the plan creation stage. It performs the following steps:
pgpro_planner searches for supported correlated subqueries.
If the pgpro_planner.memoize_check_uniqueness parameter is set to
on(default), pgpro_planner checks uniqueness for values of columns referenced in subqueries based on statistical information. This helps to determine whether theMemoizenode can be useful.The
Memoizenode can be added in the following cases:A unique index on a column does not exist, and column values are not unique according to statistical information.
A unique index on a column exists but a column contains
NULLvalues according to statistical information.A multicolumn unique index exists but values of the referenced column are not unique according to statistical information.
Statistical information for a column is not gathered yet regardless of whether an index exists.
pgpro_planner estimates the expected cost of the resulting plan with the
Memoizenode.If the estimated cost is lower than the cost of the initial plan, pgpro_planner adds the
Memoizenode into the head of the corresponding subplan.
You can disable caching of correlated subqueries by setting the pgpro_planner.memoize_subplan parameter to off, for instance, if you use correlated subqueries rarely. To disable uniqueness check for debugging purposes, set the pgpro_planner.memoize_check_uniqueness parameter to off.
G.5.4.1. Subquery Caching Example #
This example demonstrates how caching of correlated subqueries works in different cases.
Create two sample tables named t and t1 and a unique index on the x column of the first table.
CREATE TABLE t (x int, y int); INSERT INTO t SELECT id, id%20 FROM generate_series(1,1000) id; CREATE UNIQUE INDEX t_x_idx ON t (x); CREATE TABLE t1 (x1 int); INSERT INTO t1 SELECT id%2 FROM generate_series(1,1000) id;
Run the following query, where the subquery uses values of the correlated column named y. There is no unique index on this column, so the Memoize node is added to the query execution plan.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
QUERY PLAN
------------------------------------------------
Seq Scan on t
Filter: (SubPlan 1)
SubPlan 1
-> Memoize
Cache Key: t.y
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.y)
(10 rows)
Run another query, where the subquery uses values of the correlated column named x. The unique index on this column exists but statistical information is not gathered yet, so the Memoize node is generated.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
------------------------------------------------
Seq Scan on t
Filter: (SubPlan 1)
SubPlan 1
-> Memoize
Cache Key: t.x
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(10 rows)
Execute the same query once again. Statistical information for the x column is now gathered, and the unique index still exists, so the Memoize node is not added to the plan.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (x = (SubPlan 1))
SubPlan 1
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(7 rows)
Remove the previously created index and create a new multicolumn index on both columns named x and y.
CREATE UNIQUE INDEX on t(x,y); DROP INDEX t_x_idx;
Run the query referencing the correlated y column. Although the multicolumn unique index now exists, values of this column are not unique according to statistical information. The Memoize node is added to the resulting plan.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.y LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
SubPlan 1
-> Memoize
Cache Key: t.y
Cache Mode: binary
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.y)
(10 rows)
Run the query referencing the correlated x column. For this case, the Memoize node is not still generated, since values of this column are unique according to statistical information.
EXPLAIN (COSTS OFF)
SELECT x FROM t WHERE (t.x,t.y) = (SELECT sum(x1),1 FROM t1 WHERE x1 = t.x LIMIT 1);
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on t
Filter: (((x = (SubPlan 1).col1) AND (y = (SubPlan 1).col2)))
SubPlan 1
-> Limit
-> Aggregate
-> Seq Scan on t1
Filter: (x1 = t.x)
(7 rows)
G.5.4.2. Subquery Caching Limitations #
pgpro_planner cannot cache results of a correlated subquery in the following cases:
A subquery uses volatile functions.
A subquery uses non-standard operators.
A subquery uses operands with different types without compatible hash operator classes.
A subquery uses the
minormaxaggregate functions.A query plan tree does not contain the corresponding subplan node.
The estimated cost of the target query plan with the
Memoizenodes is greater than the cost of the initial plan.
G.5.5. Configuration Parameters #
-
pgpro_planner.enable(boolean) # Enables or disables the pgpro_planner extension. The default value is
offto avoid implicit or unexpected query plan changes.-
pgpro_planner.enable_simplify_trivials(boolean) # Enables or disables simplification of trivial arithmetic operations. The default value is
on.-
pgpro_planner.memoize_subplan(boolean) # Enables or disables caching of correlated subqueries. The default value is
on.-
pgpro_planner.memoize_check_uniqueness(boolean) # Enables or disables the smart logic to check uniqueness of column values for caching of correlated subqueries. The default value is
on.-
pgpro_planner.force_memoize_subplan(boolean) # Enables or disables automatic addition of the
Memoizenode for all correlated subqueries ignoring the plan cost model and uniqueness check. This parameter is designed only for debugging purposes, do not use it in production. The default value isoff.