pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16855: No partition pruning when using partitions with custom hash function
3+ messages / 3 participants
[nested] [flat]

* BUG #16855: No partition pruning when using partitions with custom hash function
@ 2021-02-05 10:44 PG Bug reporting form <noreply@postgresql.org>
  2021-02-05 16:56 ` Re: BUG #16855: No partition pruning when using partitions with custom hash function Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2021-02-05 10:44 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: stanislaw.skonieczny@gmail.com

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16855: No partition pruning when using partitions with custom hash function
  2021-02-05 10:44 BUG #16855: No partition pruning when using partitions with custom hash function PG Bug reporting form <noreply@postgresql.org>
@ 2021-02-05 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-02 14:12   ` Re: BUG #16855: No partition pruning when using partitions with custom hash function Michał Albrycht <michalalbrycht@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2021-02-05 16:56 UTC (permalink / raw)
  To: stanislaw.skonieczny@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #16855: No partition pruning when using partitions with custom hash function
  2021-02-05 10:44 BUG #16855: No partition pruning when using partitions with custom hash function PG Bug reporting form <noreply@postgresql.org>
  2021-02-05 16:56 ` Re: BUG #16855: No partition pruning when using partitions with custom hash function Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-02 14:12   ` Michał Albrycht <michalalbrycht@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michał Albrycht @ 2021-03-02 14:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Staszek Skonieczny <stanislaw.skonieczny@gmail.com>; pgsql-bugs@lists.postgresql.org

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

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2021-03-02 14:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-05 10:44 BUG #16855: No partition pruning when using partitions with custom hash function PG Bug reporting form <noreply@postgresql.org>
2021-02-05 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-02 14:12   ` Michał Albrycht <michalalbrycht@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox