BUG #16855: No partition pruning when using partitions with custom hash function

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #16855: No partition pruning when using partitions with custom hash function
Дата
Msg-id 16855-32d2983ea6e6b208@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #16855: No partition pruning when using partitions with custom hash function  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      16855
Logged by:          Stanisław Skonieczny
Email address:      stanislaw.skonieczny@gmail.com
PostgreSQL version: 13.1
Operating system:   Ubuntu 13.1-1.pgdg18.04+1
Description:

See this example.

show enable_partition_pruning;
--  enable_partition_pruning
-- --------------------------
--  on
-- (1 row)

CREATE OR REPLACE FUNCTION partition_custom_bigint_hash(value BIGINT, seed
BIGINT)
RETURNS BIGINT AS $$
    SELECT value;
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
-- CREATE FUNCTION

CREATE OPERATOR CLASS partition_custom_bigint_hash_op
    FOR TYPE int8
    USING hash AS
    OPERATOR 1 =,
    FUNCTION 2 partition_custom_bigint_hash(BIGINT, BIGINT);
-- CREATE OPERATOR CLASS

create table sample(part_id bigint) partition by hash(part_id
partition_custom_bigint_hash_op);
-- CREATE TABLE

create table sample_part_1 partition of sample for values with (modulus 3,
remainder 0);
-- CREATE TABLE

create table sample_part_2 partition of sample for values with (modulus 3,
remainder 1);
-- CREATE TABLE

create table sample_part_3 partition of sample for values with (modulus 3,
remainder 2);
-- CREATE TABLE

\d+ sample;
--                             Partitioned table "public.sample"
--  Column  |  Type  | Collation | Nullable | Default | Storage | Stats
target | Description
--
---------+--------+-----------+----------+---------+---------+--------------+-------------
--  part_id | bigint |           |          |         | plain   |
  |
-- Partition key: HASH (part_id partition_custom_bigint_hash_op)
-- Partitions: sample_part_1 FOR VALUES WITH (modulus 3, remainder 0),
--             sample_part_2 FOR VALUES WITH (modulus 3, remainder 1),
--             sample_part_3 FOR VALUES WITH (modulus 3, remainder 2)

explain select * from sample where part_id = 1;
--                                   QUERY PLAN
--
------------------------------------------------------------------------------
--  Append  (cost=0.00..101.36 rows=33 width=8)
--    ->  Seq Scan on sample_part_1 sample_1  (cost=0.00..33.73 rows=11
width=8)
--          Filter: (part_id = 1)
--    ->  Seq Scan on sample_part_2 sample_2  (cost=0.00..33.73 rows=11
width=8)
--          Filter: (part_id = 1)
--    ->  Seq Scan on sample_part_3 sample_3  (cost=0.00..33.73 rows=11
width=8)
--          Filter: (part_id = 1)
-- (7 rows)

I'd expect that only one partition will be scanned, other 2 will be removed
by partition pruning.
Example above works as expected when I remove custom hash function.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Etsuro Fujita
Дата:
Сообщение: Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
Следующее
От: Fujii Masao
Дата:
Сообщение: Re: BUG #16854: initdb fails on ReFS and FAT32 file systems