Re: One Partition by list is always chosen by planner

Hello,

It seems to me that the type of category is not text and the
right side in the query is actually casted to text.

At Tue, 21 Nov 2017 12:23:02 -0700 (MST), legrand legrand <legrand_legrand(at)hotmail(dot)com> wrote in <1511292182893-0(dot)post(at)n3(dot)nabble(dot)com>
> and if I explain the same query with an other filter
>
> explain select * from wiki_data_part where category='fr'
>
> | Append (cost=0.00..14010.76 rows=291609 width=48)
> | -> Seq Scan on wiki_data_part_f (cost=0.00..9975.04 rows=291339
> width=48)
> | Filter: ((category)::text = 'fr'::text)
> | -> Seq Scan on wiki_data_part_s (cost=0.00..4035.72 rows=270 width=50)
> | Filter: ((category)::text = 'fr'::text)
>
> wiki_data_part_s is always chosen in the plan

> | Filter: ((category)::text = 'fr'::text)

"::text" on the left side hinders constraint-exclusion.

Usually the left side won't get casted unless the right side is
explicitly casted. Otherwise sometimes the left side gets casted
so that it can be compared with the right side.

A self-contained example is shown below.

=====
create table p1 (cat char(1), b int) partition by list(cat);
create table c11 partition of p1 for values in ('a', 'b', 'c');
create table c12 partition of p1 for values in ('x', 'y', 'z');
explain select * from p1 where cat = 'b';
| QUERY PLAN
| ------------------------------------------------------------
| Append (cost=0.00..35.50 rows=10 width=12)
| -> Seq Scan on c11 (cost=0.00..35.50 rows=10 width=12)
| Filter: (cat = 'b'::bpchar)
| (3 rows)
explain select * from p1 where cat = 'b'::text;
| QUERY PLAN
| ------------------------------------------------------------
| Append (cost=0.00..81.20 rows=20 width=12)
| -> Seq Scan on c11 (cost=0.00..40.60 rows=10 width=12)
| Filter: ((cat)::text = 'b'::text)
| -> Seq Scan on c12 (cost=0.00..40.60 rows=10 width=12)
| Filter: ((cat)::text = 'b'::text)
| (5 rows)

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center