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

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.

PG Bug reporting form <noreply@postgresql.org> writes:
> I'd expect that only one partition will be scanned, other 2 will be removed
> by partition pruning.

It works if you write

regression=# explain select * from sample where part_id = 1::bigint;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on sample_part_1 sample  (cost=0.00..38.25 rows=11 width=8)
   Filter: (part_id = '1'::bigint)
(2 rows)

As it stands, the query uses the "bigint = integer" operator,
which you have not included in your custom opclass.

            regards, tom lane