Re: Декларативное партицирование - Mailing list pgsql-ru-general

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

вт, 15 окт. 2019 г., 12:22 Вавржин Игорь <igor.vavrjin@gmail.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