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