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
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