Обсуждение: UPDATE WHERE SELECT по париционированным таблицам
Тевирп! Помогите, плз., разобраться с работой UPDATE WHERE SELECT по партиционированной таблице. CREATE TABLE example ( id bigint NOT NULL, partition_key bigint NOT NULL, value numeric NOT NULL, PRIMARY KEY (id, partition_key) ) PARTITION BY LIST (partition_key); CREATE TABLE example__0 PARTITION OF example FOR VALUES IN (0); CREATE TABLE example__1 PARTITION OF example FOR VALUES IN (1); CREATE TABLE example__2 PARTITION OF example FOR VALUES IN (2); CREATE TABLE example__3 PARTITION OF example FOR VALUES IN (3); CREATE TABLE example__4 PARTITION OF example FOR VALUES IN (4); CREATE TABLE example__5 PARTITION OF example FOR VALUES IN (5); CREATE TABLE example__6 PARTITION OF example FOR VALUES IN (6); CREATE TABLE example__7 PARTITION OF example FOR VALUES IN (7); INSERT INTO example (id, partition_key, value) SELECT generate_series(1, 1000000), floor(random() * 8), random(); CREATE INDEX example_value_idx ON example (value); EXPLAIN ANALYZE UPDATE example SET value = random() WHERE (id, partition_key) IN ( SELECT id, partition_key FROM example WHERE partition_key IN (1, 3, 4, 7) -- parameterized list ORDER BY value LIMIT 1 ) RETURNING id; В результате по плану запроса UPDATE работает независимо для каждой partition и подзапрос выполняется столько раз, сколькопартиции имеет таблица. А ожидалось, что выполнится один раз подзапрос , по которому однозначно определится партициядля обновления данных. Если запрос переписать следующим способом EXPLAIN ANALYZE WITH nextValue AS ( SELECT id, partition_key FROM example WHERE partition_key IN (1, 3, 4, 7) ORDER BY value LIMIT 1 ) UPDATE example SET value = random() WHERE (id, partition_key) IN ( SELECT id, partition_key FROM nextValue ) RETURNING id , то план выполнение не меняется, и CTE инлайнится независимо для каждой партиции. Уважаемое сообщество, есть ли предложения по оптимизации плана запроса через модификацию запроса? Или такие оптимизации возможнытолько через внесение изменения в код postgres-а?
20.08.2020, 14:51, <ns@safeprivateplace.com>
Тевирп!
Помогите, плз., разобраться с работой UPDATE WHERE SELECT по партиционированной таблице.
CREATE TABLE example
(
id bigint NOT NULL,
partition_key bigint NOT NULL,
value numeric NOT NULL,
PRIMARY KEY (id, partition_key)
) PARTITION BY LIST (partition_key);
CREATE TABLE example__0 PARTITION OF example FOR VALUES IN (0);
CREATE TABLE example__1 PARTITION OF example FOR VALUES IN (1);
CREATE TABLE example__2 PARTITION OF example FOR VALUES IN (2);
CREATE TABLE example__3 PARTITION OF example FOR VALUES IN (3);
CREATE TABLE example__4 PARTITION OF example FOR VALUES IN (4);
CREATE TABLE example__5 PARTITION OF example FOR VALUES IN (5);
CREATE TABLE example__6 PARTITION OF example FOR VALUES IN (6);
CREATE TABLE example__7 PARTITION OF example FOR VALUES IN (7);
INSERT INTO example (id, partition_key, value)
SELECT generate_series(1, 1000000), floor(random() * 8), random();
CREATE INDEX example_value_idx ON example (value);
EXPLAIN ANALYZE
UPDATE example
SET value = random()
WHERE (id, partition_key) IN (
SELECT id, partition_key
FROM example
WHERE partition_key IN (1, 3, 4, 7) -- parameterized list
ORDER BY value
LIMIT 1
)
RETURNING id;
В результате по плану запроса UPDATE работает независимо для каждой partition и подзапрос выполняется столько раз, сколько партиции имеет таблица. А ожидалось, что выполнится один раз подзапрос , по которому однозначно определится партиция для обновления данных.
Если запрос переписать следующим способом
EXPLAIN ANALYZE
WITH nextValue AS (
SELECT id, partition_key
FROM example
WHERE partition_key IN (1, 3, 4, 7)
ORDER BY value
LIMIT 1
)
UPDATE example
SET value = random()
WHERE (id, partition_key) IN (
SELECT id, partition_key FROM nextValue
)
RETURNING id
, то план выполнение не меняется, и CTE инлайнится независимо для каждой партиции.
Уважаемое сообщество, есть ли предложения по оптимизации плана запроса через модификацию запроса? Или такие оптимизации возможны только через внесение изменения в код postgres-а?
Можно WITH nextValue AS MATERIALIZED для 12+, тогда инлайниться не будет.
А так update выполняет partition pruning на стадии планирования, вроде, получается, что неизвестно, какой раздел изменять придется, поэтому для всех разделов индекс скан по value проводится. Ну и, как водится, оптимизацию для any не завезли.
Aleksey M Boltenkov.
20.08.2020, 16:27, Aleksey M Boltenkov <holybolt@rambler.ru>20.08.2020, 14:51, <ns@safeprivateplace.com>
Тевирп!
Помогите, плз., разобраться с работой UPDATE WHERE SELECT по партиционированной таблице.
CREATE TABLE example
(
id bigint NOT NULL,
partition_key bigint NOT NULL,
value numeric NOT NULL,
PRIMARY KEY (id, partition_key)
) PARTITION BY LIST (partition_key);
CREATE TABLE example__0 PARTITION OF example FOR VALUES IN (0);
CREATE TABLE example__1 PARTITION OF example FOR VALUES IN (1);
CREATE TABLE example__2 PARTITION OF example FOR VALUES IN (2);
CREATE TABLE example__3 PARTITION OF example FOR VALUES IN (3);
CREATE TABLE example__4 PARTITION OF example FOR VALUES IN (4);
CREATE TABLE example__5 PARTITION OF example FOR VALUES IN (5);
CREATE TABLE example__6 PARTITION OF example FOR VALUES IN (6);
CREATE TABLE example__7 PARTITION OF example FOR VALUES IN (7);
INSERT INTO example (id, partition_key, value)
SELECT generate_series(1, 1000000), floor(random() * 8), random();
CREATE INDEX example_value_idx ON example (value);
EXPLAIN ANALYZE
UPDATE example
SET value = random()
WHERE (id, partition_key) IN (
SELECT id, partition_key
FROM example
WHERE partition_key IN (1, 3, 4, 7) -- parameterized list
ORDER BY value
LIMIT 1
)
RETURNING id;
В результате по плану запроса UPDATE работает независимо для каждой partition и подзапрос выполняется столько раз, сколько партиции имеет таблица. А ожидалось, что выполнится один раз подзапрос , по которому однозначно определится партиция для обновления данных.
Если запрос переписать следующим способом
EXPLAIN ANALYZE
WITH nextValue AS (
SELECT id, partition_key
FROM example
WHERE partition_key IN (1, 3, 4, 7)
ORDER BY value
LIMIT 1
)
UPDATE example
SET value = random()
WHERE (id, partition_key) IN (
SELECT id, partition_key FROM nextValue
)
RETURNING id
, то план выполнение не меняется, и CTE инлайнится независимо для каждой партиции.
Уважаемое сообщество, есть ли предложения по оптимизации плана запроса через модификацию запроса? Или такие оптимизации возможны только через внесение изменения в код postgres-а?Можно WITH nextValue AS MATERIALIZED для 12+, тогда инлайниться не будет.А так update выполняет partition pruning на стадии планирования, вроде, получается, что неизвестно, какой раздел изменять придется, поэтому для всех разделов индекс скан по value проводится. Ну и, как водится, оптимизацию для any не завезли.Aleksey M Boltenkov.
В смысле по pkey, конечно.
Index Cond: ((id = nextvalue.id) AND (partition_key = nextvalue.partition_key))
Aleksey M Boltenkov.