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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
