Обсуждение: One Partition by list is always chosen by planner
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
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@hotmail.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.
Ing. Hellmuth I. Vargas S.
yes it is show constraint_exclusion partition 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 -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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@hotmail.com> wrote in <1511292182893-0.post@n3.nabble.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
Hello, thank you for this answer. I just wanted to add a few informations. This table has in fact around 20 partitions, explain select * from wiki_data_part where category='fr' returns only 2 partitions (meaning that constraint_exclusion works) the partition that is always scanned has more than 100 distinct values in its partition list. After splitting this partition into 2 partitions the problem is gone ... For me this is as if the planner was not able to check partitions with too much values. There is no error with the query result, just a limited impact on performances. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html