Re: One Partition by list is always chosen by planner

Hi

what is the value of the parameter "constraint_exclusion" (in
postgresq.conf)?

You must set:

constraint_exclusion = partition

2017-11-20 17:54 GMT-05:00 legrand legrand <legrand_legrand(at)hotmail(dot)com>:

> Hello,
>
> after creating a table wiki_data_part with
> partition by list (category);
>
> and creating partitions like
> CREATE TABLE wiki_data_part_a PARTITION OF wiki_data_part
> FOR VALUES IN ('ang.q',...,'arc');
> CREATE TABLE wiki_data_part_b PARTITION OF wiki_data_part
> FOR VALUES IN ('bs.s',...,'bg.n');
>
> copy table wiki_data_part from ...;
>
> analyze wiki_data_part;
>
> explain select * from wiki_data_part where category='en'
>
> | Append (cost=0.00..21595.75 rows=4 width=102)
> | -> Seq Scan on wiki_data_part_e (cost=0.00..21578.00 rows=1
> width=102)
> | Filter: ((category)::text = 'en'::text)
> | -> Seq Scan on wiki_data_part_s (cost=0.00..17.75 rows=3 width=102)
> | Filter: ((category)::text = 'en'::text)
>
> partition wiki_data_part_s (that has more than 100 values in its list) is
> always scanned,
> even when where predicates are not in its values list ...
>
> Problem occurs on
> PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
> even without data loaded.
>
> If this is a problem of max values, maybe this could be added in doc ?
>
>
> wiki_data_wrong_part_s_chosen.sql
> <http://www.postgresql-archive.org/file/t348768/wiki_
> data_wrong_part_s_chosen.sql>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
> f1843780.html
>
>

--
Cordialmente,

Ing. Hellmuth I. Vargas S.