Re: BUG #15093: hash partition exector plan is error!!!!

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(at)163(dot)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/