Оптимизация механизма «отсечение партиций» для таблиц с композитным ключом партиционирования
Описание
Функциональность предоставляет возможность настраивать поведение механизма «отсечение партиций» для каждой отдельной партиционированной таблицы с композитным ключом партиционирования. Это дает возможность формировать план запроса с более естественным и логичным выбором партиций по сравнению с исходным алгоритмом. Сокращение количества задействованных партиций в плане запроса положительно влияет на производительность.
В синтаксис DDL-запросов CREATE TABLE и ALTER TABLE добавлен параметр pruning_fields. Он позволяет указывать те столбцы композитного ключа партиционирования, которые будут учитываться доработанным механизмом «отсечение партиций». Такие столбцы используются в дополнение к тем, что отбирает оригинальный алгоритм (например, первый столбец композитного ключа). Если значение pruning_fields не задано или имеет пустое значение, применяется исходный алгоритм без изменений.
Функциональность доступна только для редакций Enterprise и Enterprise для ERP-систем.
Настройка
Пример создания таблицы с новым параметром pruning_fields и композитным ключом партиционирования, состоящим из четырех столбцов:
CREATE TABLE test_new_pruning(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH (pruning_fields = 'key1, key4');
Параметр pruning_fields индивидуален для каждой партиционированной таблицы. Его значение можно изменить с помощью команды ALTER TABLE — как в случае, если параметр был указан при создании таблицы, так и если изначально он не задавался:
CREATE TABLE test_new_pruning(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4);
ALTER TABLE test_new_pruning SET (pruning_fields='key2, key3');
CREATE TABLE test_new_pruning1(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH (pruning_fields = 'key4');
ALTER TABLE test_new_pruning1 SET (pruning_fields='key2, key3');
Посмотреть значение параметра pruning_fields можно в системной таблице pg_class или запросом \d+:
SELECT reloptions FROM pg_class WHERE relname='test_new_pruning';
reloptions
-------------------------------
{"pruning_fields=key2, key3"}
(1 row)
\d+ test_new_pruning
Partitioned table "public.test_new_pruning"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
key1 | integer | | not null | | plain | | |
key2 | character varying(3) | | not null | | extended | | |
key3 | character varying(3) | | not null | | extended | | |
key4 | integer | | not null | | plain | | |
key5 | date | | not null | | plain | | |
Partition key: RANGE (key1, key2, key3, key4)
Number of partitions: 0
Options: pruning_fields=key2, key3
Запросы CREATE TABLE ... WITH и ALTER TABLE SET заканчиваются ошибкой, если параметр pruning_fields указан без скобок или без знака равенства:
CREATE TABLE test_new_pruning2(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH pruning_fields = 'key4';
ERROR: syntax error at or near "pruning_fields"
ALTER TABLE test_new_pruning SET pruning_fields='key3';
ERROR: syntax error at or near "pruning_fields"
CREATE TABLE test_new_pruning2(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH (pruning_fields 'key4');
ERROR: syntax error at or near "'key4'"
ALTER TABLE test_new_pruning SET (pruning_fields 'key3');
Error: syntax error at or near "'key3'"
Столбцы в параметре pruning_fields необходимо указывать в одинарных кавычках. Если указать их в двойных кавычках, выполнение запроса закончится ошибкой:
ALTER TABLE test_new_pruning SET (pruning_fields="key3");
ERROR: "pruning_fields" accepts String type argument only
При попытке добавить в параметр pruning_fields несуществующий столбец или столбец, не входящий в композитный ключ партиционирования, запросы CREATE TABLE и ALTER TABLE SET заканчиваются ошибкой:
CREATE TABLE test_new_pruning1(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH (pruning_fields='fake_column');
ERROR: unrecognized pruning_field column: "fake_column"
CREATE TABLE test_new_pruning1(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4);
ALTER TABLE test_new_pruning1 SET (pruning_fields='fake_column');
ERROR: unrecognized pruning_field column: "fake_column"
CREATE TABLE test_new_pruning2(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4) WITH (pruning_fields='key5');
ERROR: pruning_field column: "key5" is not a partition key
ALTER TABLE test_new_pruning1 SET (pruning_fields='key5');
ERROR: pruning_field column: "key5" is not a partition key
Пример работы доработанного алгоритма «отсечение партиций»:
CREATE TABLE test_new_pruning(
key1 INT NOT NULL,
key2 VARCHAR(3) NOT NULL,
key3 VARCHAR(3) NOT NULL,
key4 INT NOT NULL,
key5 DATE NOT NULL)
PARTITION BY RANGE (key1,key2,key3,key4)
WITH (pruning_fields = 'key1, key4');
CREATE TABLE test_new_pruning10_A_W_0 PARTITION OF test_new_pruning FOR VALUES FROM (10, 'A', 'W', 0) TO (19, 'A', 'W', 0);
CREATE TABLE test_new_pruning20_A_X_1 PARTITION OF test_new_pruning FOR VALUES FROM (20, 'A', 'X', 1) TO (29, 'A', 'X', 1);
CREATE TABLE test_new_pruning30_A_Y_1 PARTITION OF test_new_pruning FOR VALUES FROM (30, 'A', 'Y', 1) TO (39, 'A', 'Y', 1);
CREATE TABLE test_new_pruning40_A_Z_1 PARTITION OF test_new_pruning FOR VALUES FROM (40, 'A', 'Z', 1) TO (49, 'A', 'Z', 1);
CREATE TABLE test_new_pruning50_A_V_2 PARTITION OF test_new_pruning FOR VALUES FROM (50, 'A', 'V', 2) TO (59, 'A', 'V', 2);
CREATE TABLE test_new_pruning60_B_W_2 PARTITION OF test_new_pruning FOR VALUES FROM (60, 'B', 'W', 2) TO (69, 'B', 'W', 2);
CREATE TABLE test_new_pruning70_B_X_2 PARTITION OF test_new_pruning FOR VALUES FROM (70, 'B', 'X', 2) TO (79, 'B', 'X', 2);
CREATE TABLE test_new_pruning80_B_Y_3 PARTITION OF test_new_pruning FOR VALUES FROM (80, 'B', 'Y', 3) TO (89, 'B', 'Y', 3);
CREATE TABLE test_new_pruning90_B_Z_3 PARTITION OF test_new_pruning FOR VALUES FROM (90, 'B', 'Z', 3) TO (99, 'B', 'Z', 3);
CREATE TABLE test_new_pruning100_B_V_3 PARTITION OF test_new_pruning FOR VALUES FROM (100, 'B', 'V', 3) TO (109, 'B', 'V', 3);
EXPLAIN (costs off) SELECT count(*) FROM test_new_pruning WHERE key4 < 3 AND key1 >= 30;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on test_new_pruning30_a_y_1 test_new_pruning_1
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning40_a_z_1 test_new_pruning_2
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning50_a_v_2 test_new_pruning_3
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning60_b_w_2 test_new_pruning_4
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning70_b_x_2 test_new_pruning_5
Filter: ((key4 < 3) AND (key1 >= 30))
(12 rows)
EXPLAIN (costs off) SELECT count(*) FROM test_new_pruning WHERE key4 > 0 AND key1 < 55;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on test_new_pruning20_a_x_1 test_new_pruning_1
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning30_a_y_1 test_new_pruning_2
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning40_a_z_1 test_new_pruning_3
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning50_a_v_2 test_new_pruning_4
Filter: ((key4 > 0) AND (key1 < 55))
(10 rows)
Доработанный алгоритм «отсечение партиций» не исключает столбцы, задействованные оригинальным алгоритмом. В данном примере первый столбец композитного ключа партиционирования учитывается, хотя он не задан в параметре pruning_fields:
ALTER TABLE test_new_pruning SET (pruning_fields='key4');
ALTER TABLE
SELECT reloptions FROM pg_class WHERE relname='test_new_pruning';
reloptions
-----------------------
{pruning_fields=key4}
(1 row)
EXPLAIN (costs off) SELECT count(*) FROM test_new_pruning WHERE key4 < 3 AND key1 >= 30;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on test_new_pruning30_a_y_1 test_new_pruning_1
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning40_a_z_1 test_new_pruning_2
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning50_a_v_2 test_new_pruning_3
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning60_b_w_2 test_new_pruning_4
Filter: ((key4 < 3) AND (key1 >= 30))
-> Seq Scan on test_new_pruning70_b_x_2 test_new_pruning_5
Filter: ((key4 < 3) AND (key1 >= 30))
(12 rows)
EXPLAIN (costs off) SELECT count(*) FROM test_new_pruning WHERE key4 > 0 AND key1 < 55;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on test_new_pruning20_a_x_1 test_new_pruning_1
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning30_a_y_1 test_new_pruning_2
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning40_a_z_1 test_new_pruning_3
Filter: ((key4 > 0) AND (key1 < 55))
-> Seq Scan on test_new_pruning50_a_v_2 test_new_pruning_4
Filter: ((key4 > 0) AND (key1 < 55))
(10 rows)
Если в параметре pruning_fields задать пустую строку, то доработанный алгоритм «отсечение партиций» отключается, при планировании запроса используется только исходный алгоритм механизма:
ALTER TABLE test_new_pruning SET (pruning_fields='');
ALTER TABLE
SELECT reloptions FROM pg_class WHERE relname='test_new_pruning';
reloptions
-------------------
{pruning_fields=}
(1 row)
EXPLAIN (costs off) SELECT count(*) FROM test_new_pruning WHERE key4 < 3 AND key1 >= 55;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Append
-> Seq Scan on test_new_pruning50_a_v_2 test_new_pruning_1
Filter: ((key4 < 3) AND (key1 >= 55))
-> Seq Scan on test_new_pruning60_b_w_2 test_new_pruning_2
Filter: ((key4 < 3) AND (key1 >= 55))
-> Seq Scan on test_new_pruning70_b_x_2 test_new_pruning_3
Filter: ((key4 < 3) AND (key1 >= 55))
-> Seq Scan on test_new_pruning80_b_y_3 test_new_pruning_4
Filter: ((key4 < 3) AND (key1 >= 55))
-> Seq Scan on test_new_pruning90_b_z_3 test_new_pruning_5
Filter: ((key4 < 3) AND (key1 >= 55))
-> Seq Scan on test_new_pruning100_b_v_3 test_new_pruning_6
Filter: ((key4 < 3) AND (key1 >= 55))
(14 rows)
Управление
Логирование
Возможные коды ошибок во время валидации передаваемых в reloption значений в validate_string_pruning_fields:
-
ошибка при отсутствии необходимой лицензии:
pruning_fields option needs Enterprise license level -
ошибка при попытке разделить лист значений:
invalid list syntax in pruning_fields op -
ошибка при отсутствии листа значений (было передано пустое значение в
pruning_fields):no keys found in pruning_fields option
Во время поиска границ партиций – ошибка при неожиданном номере стратегии:
invalid strategy number