Re: Constraint exclusion failed to prune partition in case of partition expression involves function call

On 2017/02/02 21:09, amul sul wrote:
> Hi,
>
> In following case, constraint exclusion not able prune partition (even
> if function is immutable), is this know behaviour?

Yes. The where condition in your example query does not specify the
partition key column, so constraint exclusion won't work, which requires
variable in the condition to be spelled out exactly same as the partition
key column. Here the partitioning code is going to return check
constraints of the form abs(a) = 0 for foo_list1, abs(a) = 1 for foo_list2
and so on, for the constraint exclusion logic to work upon.

> --Explain plan
> postgres=# explain select * from foo_list where a = 2;
> QUERY PLAN
> -----------------------------------------------------------------
> Append (cost=0.00..103.50 rows=25 width=36)
> -> Seq Scan on foo_list (cost=0.00..0.00 rows=1 width=36)
> Filter: (a = 2)
> -> Seq Scan on foo_list1 (cost=0.00..25.88 rows=6 width=36)
> Filter: (a = 2)
> -> Seq Scan on foo_list2 (cost=0.00..25.88 rows=6 width=36)
> Filter: (a = 2)
> -> Seq Scan on foo_list3 (cost=0.00..25.88 rows=6 width=36)
> Filter: (a = 2)
> -> Seq Scan on foo_list4 (cost=0.00..25.88 rows=6 width=36)
> Filter: (a = 2)
> (11 rows)

If you try with where abs(a) = 2, it works:

explain select * from foo_list where abs(a) = 2;
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..29.05 rows=7 width=36)
-> Seq Scan on foo_list (cost=0.00..0.00 rows=1 width=36)
Filter: (abs(a) = 2)
-> Seq Scan on foo_list3 (cost=0.00..29.05 rows=6 width=36)
Filter: (abs(a) = 2)
(5 rows)

See an old exchange at the link below for a kind of similar example and
some explanations about why the thing that one thinks would or should
happen doesn't happen.

https://www.postgresql.org/message-id/CA%2BTgmoaE9NZ_RiqZQLp2aJXPO4E78QxkQYL-FR2zCDop96Ahdg%40mail.gmail.com

Thanks,
Amit