Re: Декларативное партицирование

Совсем забыл, постгрес 11, но если есть объяснения и решения для 12 - также
буду рад услышать.

вт, 15 окт. 2019 г., 12:22 Вавржин Игорь <igor(dot)vavrjin(at)gmail(dot)com>:

> Всем привет, есть два вопроса по партицированию - никак не могу понять,
> как их порешать...
>
> -- кастомная хэширующая функция - просто отдает значение
> 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);
>
> -- собственно партицируемая таблица с разбиением по хэшу с кастомным
> классом оператора
> create table hash_parted (
> 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
>
> такое ощущение что хэш считается не по моей функции!!!
>
> вопрос номер два:
>
> можно ли при разбиении по хэшу (из примера выше) сделать запрос на выборку
> скажем четных значений, что бы планировщик ходил ТОЛЬКО в нужные партиции?
>
> 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
>
>
> Запрос уходит в партицию только в случае если передать конкретные значения
> 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);
>
> 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
>
>