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