Декларативное партицирование
От | Вавржин Игорь |
---|---|
Тема | Декларативное партицирование |
Дата | |
Msg-id | CAHGBwKh99aJ5ZwKn5PwkBQxrGJyvf6_TJtr6R2AHyS6UyP242g@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Декларативное партицирование
|
Список | pgsql-ru-general |
Всем привет, есть два вопроса по партицированию - никак не могу понять, как их порешать...
returns bigint as $$
select value
-- кастомная хэширующая функция - просто отдает значение
create or replace function hash_bigint(value bigint, seed bigint)returns bigint as $$
select value
$$ language sql immutable;
-- класс операторов использующих hash и кастомную финкцию хэширования
create operator class bigint_ops
for type bigint
using hash as
operator 1 =,
function 2 hash_bigint(bigint, bigint);
for type bigint
using hash as
operator 1 =,
function 2 hash_bigint(bigint, bigint);
-- собственно партицируемая таблица с разбиением по хэшу с кастомным классом оператора
create table hash_parted (
a bigint
) partition by hash (a bigint_ops);
a bigint
) partition by hash (a bigint_ops);
-- партиции, 5 штук с остатками от деления по мод 5
create table hash_parted_0
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 0);
create table hash_parted_1
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 1);
create table hash_parted_2
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 2);
create table hash_parted_3
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 3);
create table hash_parted_4
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 4);
а теперь вопрос номер 1:
insert into hash_parted (a) values (0::bigint), (5::bigint); -- ожидаю что все будет в партиции hash_parted_0 но все попадает в hash_parted_3
create table hash_parted_0
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 0);
create table hash_parted_1
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 1);
create table hash_parted_2
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 2);
create table hash_parted_3
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 3);
create table hash_parted_4
partition of hash_parted
FOR VALUES WITH (modulus 5, remainder 4);
а теперь вопрос номер 1:
insert into hash_parted (a) values (0::bigint), (5::bigint); -- ожидаю что все будет в партиции hash_parted_0 но все попадает в hash_parted_3
такое ощущение что хэш считается не по моей функции!!!
вопрос номер два:
можно ли при разбиении по хэшу (из примера выше) сделать запрос на выборку скажем четных значений, что бы планировщик ходил ТОЛЬКО в нужные партиции?
explain analyse select * from hash_parted where a % 5 =0;
вопрос номер два:
можно ли при разбиении по хэшу (из примера выше) сделать запрос на выборку скажем четных значений, что бы планировщик ходил ТОЛЬКО в нужные партиции?
explain analyse select * from hash_parted where a % 5 =0;
Gather (cost=1000.00..14758.93 rows=5000 width=8) (actual time=28.918..68.688 rows=200000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..13258.93 rows=2080 width=8) (actual time=34.293..48.580 rows=66667 loops=3)
-> Parallel Seq Scan on hash_parted_0 (cost=0.00..2649.71 rows=588 width=8) (actual time=27.816..27.816 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_1 (cost=0.00..2649.71 rows=588 width=8) (actual time=28.089..28.089 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_2 (cost=0.00..2649.71 rows=588 width=8) (actual time=18.721..18.721 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_3 (cost=0.00..2649.71 rows=588 width=8) (actual time=0.006..9.663 rows=66667 loops=3)
Filter: ((a % '5'::bigint) = 0)
-> Parallel Seq Scan on hash_parted_4 (cost=0.00..2649.71 rows=588 width=8) (actual time=28.226..28.226 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
Planning Time: 0.160 ms
Execution Time: 75.443 ms
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..13258.93 rows=2080 width=8) (actual time=34.293..48.580 rows=66667 loops=3)
-> Parallel Seq Scan on hash_parted_0 (cost=0.00..2649.71 rows=588 width=8) (actual time=27.816..27.816 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_1 (cost=0.00..2649.71 rows=588 width=8) (actual time=28.089..28.089 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_2 (cost=0.00..2649.71 rows=588 width=8) (actual time=18.721..18.721 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
-> Parallel Seq Scan on hash_parted_3 (cost=0.00..2649.71 rows=588 width=8) (actual time=0.006..9.663 rows=66667 loops=3)
Filter: ((a % '5'::bigint) = 0)
-> Parallel Seq Scan on hash_parted_4 (cost=0.00..2649.71 rows=588 width=8) (actual time=28.226..28.226 rows=0 loops=1)
Filter: ((a % '5'::bigint) = 0)
Rows Removed by Filter: 200000
Planning Time: 0.160 ms
Execution Time: 75.443 ms
Запрос уходит в партицию только в случае если передать конкретные значения a, причем это не работает скажем при join-ах:
explain analyse select * from hash_parted JOIN (select * from UNNEST('{0,5,44}'::BIGINT[]) as v) as t ON t.v=hash_parted.a;
Hash Join (cost=2.25..28177.25 rows=500000 width=16) (actual time=86.357..208.569 rows=2 loops=1)
Hash Cond: (hash_parted_0.a = v.v)
-> Append (cost=0.00..19425.00 rows=1000000 width=8) (actual time=0.010..138.030 rows=1000000 loops=1)
-> Seq Scan on hash_parted_0 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.009..14.978 rows=200000 loops=1)
-> Seq Scan on hash_parted_1 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.250 rows=200000 loops=1)
-> Seq Scan on hash_parted_2 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.009..13.433 rows=200000 loops=1)
-> Seq Scan on hash_parted_3 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.287 rows=200000 loops=1)
-> Seq Scan on hash_parted_4 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.337 rows=200000 loops=1)
-> Hash (cost=1.00..1.00 rows=100 width=8) (actual time=0.008..0.008 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest v (cost=0.00..1.00 rows=100 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Planning Time: 0.130 ms
Execution Time: 208.598 ms
и самое интересное вот так тоже не работает:
explain analyse select * from hash_parted where a IN (select * from UNNEST('{0,5,44}'::BIGINT[]));
Hash Semi Join (cost=2.25..27614.75 rows=500000 width=8) (actual time=84.185..206.503 rows=2 loops=1)
Hash Cond: (hash_parted_0.a = unnest.unnest)
-> Append (cost=0.00..19425.00 rows=1000000 width=8) (actual time=0.022..136.351 rows=1000000 loops=1)
-> Seq Scan on hash_parted_0 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.018..14.284 rows=200000 loops=1)
-> Seq Scan on hash_parted_1 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.442 rows=200000 loops=1)
-> Seq Scan on hash_parted_2 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.510 rows=200000 loops=1)
-> Seq Scan on hash_parted_3 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.261 rows=200000 loops=1)
-> Seq Scan on hash_parted_4 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.289 rows=200000 loops=1)
-> Hash (cost=1.00..1.00 rows=100 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest (cost=0.00..1.00 rows=100 width=8) (actual time=0.003..0.004 rows=3 loops=1)
Planning Time: 0.099 ms
Execution Time: 206.527 ms
Работает ТОЛЬКО вот так:
explain analyse select * from hash_parted where a IN (5::bigint, 44::bigint);
explain analyse select * from hash_parted JOIN (select * from UNNEST('{0,5,44}'::BIGINT[]) as v) as t ON t.v=hash_parted.a;
Hash Join (cost=2.25..28177.25 rows=500000 width=16) (actual time=86.357..208.569 rows=2 loops=1)
Hash Cond: (hash_parted_0.a = v.v)
-> Append (cost=0.00..19425.00 rows=1000000 width=8) (actual time=0.010..138.030 rows=1000000 loops=1)
-> Seq Scan on hash_parted_0 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.009..14.978 rows=200000 loops=1)
-> Seq Scan on hash_parted_1 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.250 rows=200000 loops=1)
-> Seq Scan on hash_parted_2 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.009..13.433 rows=200000 loops=1)
-> Seq Scan on hash_parted_3 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.287 rows=200000 loops=1)
-> Seq Scan on hash_parted_4 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.337 rows=200000 loops=1)
-> Hash (cost=1.00..1.00 rows=100 width=8) (actual time=0.008..0.008 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest v (cost=0.00..1.00 rows=100 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Planning Time: 0.130 ms
Execution Time: 208.598 ms
и самое интересное вот так тоже не работает:
explain analyse select * from hash_parted where a IN (select * from UNNEST('{0,5,44}'::BIGINT[]));
Hash Semi Join (cost=2.25..27614.75 rows=500000 width=8) (actual time=84.185..206.503 rows=2 loops=1)
Hash Cond: (hash_parted_0.a = unnest.unnest)
-> Append (cost=0.00..19425.00 rows=1000000 width=8) (actual time=0.022..136.351 rows=1000000 loops=1)
-> Seq Scan on hash_parted_0 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.018..14.284 rows=200000 loops=1)
-> Seq Scan on hash_parted_1 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.442 rows=200000 loops=1)
-> Seq Scan on hash_parted_2 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.510 rows=200000 loops=1)
-> Seq Scan on hash_parted_3 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.261 rows=200000 loops=1)
-> Seq Scan on hash_parted_4 (cost=0.00..2885.00 rows=200000 width=8) (actual time=0.008..13.289 rows=200000 loops=1)
-> Hash (cost=1.00..1.00 rows=100 width=8) (actual time=0.005..0.005 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest (cost=0.00..1.00 rows=100 width=8) (actual time=0.003..0.004 rows=3 loops=1)
Planning Time: 0.099 ms
Execution Time: 206.527 ms
Работает ТОЛЬКО вот так:
explain analyse select * from hash_parted where a IN (5::bigint, 44::bigint);
Gather (cost=1000.00..5711.59 rows=4 width=8) (actual time=0.500..18.191 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..4711.19 rows=2 width=8) (actual time=4.025..13.723 rows=1 loops=3)
-> Parallel Seq Scan on hash_parted_2 (cost=0.00..2355.59 rows=1 width=8) (actual time=2.298..6.539 rows=0 loops=3)
Filter: (a = ANY ('{5,44}'::bigint[]))
Rows Removed by Filter: 66666
-> Parallel Seq Scan on hash_parted_3 (cost=0.00..2355.59 rows=1 width=8) (actual time=4.312..10.773 rows=0 loops=2)
Filter: (a = ANY ('{5,44}'::bigint[]))
Rows Removed by Filter: 100000
Planning Time: 0.227 ms
Execution Time: 18.208 ms
Workers Planned: 2
Workers Launched: 2
-> Parallel Append (cost=0.00..4711.19 rows=2 width=8) (actual time=4.025..13.723 rows=1 loops=3)
-> Parallel Seq Scan on hash_parted_2 (cost=0.00..2355.59 rows=1 width=8) (actual time=2.298..6.539 rows=0 loops=3)
Filter: (a = ANY ('{5,44}'::bigint[]))
Rows Removed by Filter: 66666
-> Parallel Seq Scan on hash_parted_3 (cost=0.00..2355.59 rows=1 width=8) (actual time=4.312..10.773 rows=0 loops=2)
Filter: (a = ANY ('{5,44}'::bigint[]))
Rows Removed by Filter: 100000
Planning Time: 0.227 ms
Execution Time: 18.208 ms
В списке pgsql-ru-general по дате отправления: