[HACKERS] Constraint exclusion failed to prune partition in case of partitionexpression involves function call
От | amul sul |
---|---|
Тема | [HACKERS] Constraint exclusion failed to prune partition in case of partitionexpression involves function call |
Дата | |
Msg-id | CAAJ_b94MkOOf_N-XAxKJrm8wbnTpv586bebk3+aENgmsqinGyQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: [HACKERS] Constraint exclusion failed to prune partition in caseof partition expression involves function call
|
Список | 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 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
В списке pgsql-hackers по дате отправления: