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

Started by PG Bug reporting formabout 5 years ago3 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #16855: No partition pruning when using partitions with 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

#3Michał Albrycht
michalalbrycht@gmail.com
In reply to: Tom Lane (#2)
Re: BUG #16855: No partition pruning when using partitions with custom hash function

I'm trying to make it work I can't. How can I include next = operator in
the opclass? I would like to make partition pruning work for both int4 and
int8. Could someone help me understand what needs to be added here?
Thank you in advance for any suggestions.

Michał Albrycht

wt., 2 mar 2021 o 14:59 Tom Lane <tgl@sss.pgh.pa.us> napisał(a):

Show quoted text

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