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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: stanislaw(dot)skonieczny(at)gmail(dot)com
Subject: BUG #16855: No partition pruning when using partitions with custom hash function
Date: 2021-02-05 10:44:32
Message-ID: 16855-32d2983ea6e6b208@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16855
Logged by: Stanisław Skonieczny
Email address: stanislaw(dot)skonieczny(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Fujii Masao 2021-02-05 14:26:51 Re: BUG #16854: initdb fails on ReFS and FAT32 file systems
Previous Message Etsuro Fujita 2021-02-05 06:38:55 Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table