Thread: [HACKERS] Constraint exclusion failed to prune partition in case of partitionexpression involves function call

Hi,

In following case, constraint exclusion not able prune partition (even
if function is immutable), is this know behaviour?

--CASE 1 :  create table & insert data
create table foo_list (a integer, b text) partition by list (abs(a));
create table foo_list1 partition of foo_list for values in (0);
create table foo_list2 partition of foo_list for values in (1);
create table foo_list3 partition of foo_list for values in (2);
create table foo_list4 partition of foo_list for values in (3);
insert into foo_list values(0),(1),(-1),(2),(-2),(3),(-3);

--Explain plan
postgres=# explain select * from foo_list where a = 2;                          QUERY PLAN
-----------------------------------------------------------------Append  (cost=0.00..103.50 rows=25 width=36)  ->  Seq
Scanon 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=6width=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.88rows=6 width=36)        Filter: (a = 2)
 
(11 rows)

AFAUI, constraint exclusion should prune all above table other than
foo_list3 as happens in the following case :

-- CASE 2: create table & insert data
create table bar_list (a integer, b text) partition by list (a);
create table bar_list1 partition of bar_list for values in (0);
create table bar_list2 partition of bar_list for values in (1);
create table bar_list3 partition of bar_list for values in (2);
create table bar_list4 partition of bar_list for values in (3);
insert into bar_list values(0),(1),(2),(3);

--- Explain plan
postgres=# explain select * from bar_list where a = 2;                          QUERY PLAN
-----------------------------------------------------------------Append  (cost=0.00..25.88 rows=7 width=36)  ->  Seq
Scanon bar_list  (cost=0.00..0.00 rows=1 width=36)        Filter: (a = 2)  ->  Seq Scan on bar_list3  (cost=0.00..25.88
rows=6width=36)        Filter: (a = 2)
 
(5 rows)

Thanks & Regards,
Amul

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
Scanon foo_list  (cost=0.00..0.00 rows=1 width=36)        Filter: (abs(a) = 2)  ->  Seq Scan on foo_list3
(cost=0.00..29.05rows=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