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.