[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  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
Список 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 по дате отправления:

Предыдущее
От: Ashutosh Sharma
Дата:
Сообщение: Re: [HACKERS] pageinspect: Hash index support
Следующее
От: Kyotaro HORIGUCHI
Дата:
Сообщение: Re: [HACKERS] What is "index returned tuples in wrong order" forrecheck supposed to guard against?