Re: BUG #15093: hash partition exector plan is error!!!! - Mailing list pgsql-bugs

From Amit Langote
Subject Re: BUG #15093: hash partition exector plan is error!!!!
Date
Msg-id 963fcff1-a955-c46c-455a-923f35b58fc9@lab.ntt.co.jp
Whole thread Raw
In response to BUG #15093: hash partition exector plan is error!!!!  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
On 2018/02/27 17:38, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15093
> Logged by:          jiang fei
> Email address:      weiboyiyou@163.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   CentOS release 6.8
> Description:        
> 
> I create a table like this:
> test=# \d+ orders
>                                    Table "public.orders"
>   Column  |  Type  | Collation | Nullable | Default | Storage  | Stats
> target | Description 
> ----------+--------+-----------+----------+---------+----------+--------------+-------------
>  order_id | bigint |           | not null |         | plain    |            
>  | 
>  cust_id  | bigint |           | not null |         | plain    |            
>  | 
>  status   | text   |           |          |         | extended |            
>  | 
> Partition key: HASH (order_id)
> Partitions: orders_p1 FOR VALUES WITH (modulus 4, remainder 0),
>             orders_p2 FOR VALUES WITH (modulus 4, remainder 1),
>             orders_p3 FOR VALUES WITH (modulus 4, remainder 2),
>             orders_p4 FOR VALUES WITH (modulus 4, remainder 3)
> 
> 
> but the exector plan is:
> test=# explain select * from orders where order_id = 1;
>                            QUERY PLAN                            
> -----------------------------------------------------------------
>  Append  (cost=0.00..93.60 rows=20 width=48)
>    ->  Seq Scan on orders_p1  (cost=0.00..23.38 rows=5 width=48)
>          Filter: (order_id = 1)
>    ->  Seq Scan on orders_p2  (cost=0.00..23.38 rows=5 width=48)
>          Filter: (order_id = 1)
>    ->  Seq Scan on orders_p3  (cost=0.00..23.38 rows=5 width=48)
>          Filter: (order_id = 1)
>    ->  Seq Scan on orders_p4  (cost=0.00..23.38 rows=5 width=48)
>          Filter: (order_id = 1)

That's expected output, as of now.  You would think that partition pruning
should have eliminated all partitions but one, but the current method of
partition pruning (called constraint exclusion) does not work with hash
partitioning, so you get a plan containing all partitions.

We're working on [1] replacing constraint exclusion as the partition
pruning mechanism for Postgres 11 (which is still being developed, as you
might know) and that should result in a plan containing only the desired
partition.

Thanks,
Amit

[1] https://commitfest.postgresql.org/17/1272/



pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15093: hash partition exector plan is error!!!!
Next
From: Praveen Kumar
Date:
Subject: How to avoid trailing zero (after decimal point) for numeric type column