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

> такое ощущение что хэш считается не по моей функции!!!
Правильное ощущение. Для кастомной функции нужно range/list делать. С 12
версии в класс for values добавили возможность использования immutable
функций:

create function h ( val bigint ) returns bigint as $$ select val % 2; $$
language sql immutable;
create table p ( a bigint , val bigint ) partition by list ( h(a) );
create table p0 partition of p for values in ( 0 );
create table p1 partition of p for values in ( 1 );

insert into p select x from generate_series(1, 10e6) x;

explain (analyze, timing off) select * from p where h(a) = 0;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Seq Scan on p0  (cost=0.00..97124.00 rows=25000 width=16) (actual
rows=5000000 loops=1)
   Filter: ((a % '2'::bigint) = 0)
 Planning Time: 0.258 ms
 Execution Time: 538.404 ms
(4 rows)

explain (analyze, timing off) select * from p where h(a) = 1;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Seq Scan on p1  (cost=0.00..97124.00 rows=25000 width=16) (actual
rows=5000000 loops=1)
   Filter: ((a % '2'::bigint) = 1)
 Planning Time: 0.266 ms
 Execution Time: 551.930 ms
(4 rows)

select version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Gentoo
Hardened 9.2.0-r1 p2) 9.2.0, 64-bit
(1 row)

On 10/15/19 08:32, Вавржин Игорь wrote:
> Совсем забыл, постгрес 11, но если есть объяснения и решения для 12 -
> также буду рад услышать.
>
> вт, 15 окт. 2019 г., 12:22 Вавржин Игорь <igor(dot)vavrjin(at)gmail(dot)com
> <mailto: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
>