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

From: amul sul <sulamul(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Constraint exclusion failed to prune partition in case of partition expression involves function call
Date: 2017-02-02 12:09:54
Message-ID: CAAJ_b94MkOOf_N-XAxKJrm8wbnTpv586bebk3+aENgmsqinGyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

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 Scan on 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=6 width=36)
Filter: (a = 2)
(5 rows)

Thanks & Regards,
Amul

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2017-02-02 12:11:16 Re: What is "index returned tuples in wrong order" for recheck supposed to guard against?
Previous Message Ashutosh Sharma 2017-02-02 11:29:04 Re: pageinspect: Hash index support