Amit-san, > -----Original Message----- > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > Sent: Friday, April 05, 2019 6:47 PM > To: Yuzuko Hosoya <hosoya.yuz...@lab.ntt.co.jp>; 'Thibaut' > <thibaut.madela...@dalibo.com>; 'Imai, > Yoshikazu' <imai.yoshik...@jp.fujitsu.com> > Cc: 'PostgreSQL Hackers' <pgsql-hackers@lists.postgresql.org> > Subject: Re: Problem with default partition pruning > > Hosoya-san, > > > On 2019/04/04 13:00, Yuzuko Hosoya wrote: > > I added some test cases to each patch according to tests discussed in > > this thread. > > Thanks a lot. > > > However, I found another problem as follows. This query should output > > "One-Time Filter: false" because rlp3's constraints contradict WHERE > > clause. > > > > ----- > > postgres=# \d+ rlp3 > > Partitioned table "public.rlp3" > > Column | Type | Collation | Nullable | Default | Storage | > > Stats target | Description > > > --------+-------------------+-----------+----------+---------+----------+--------------+--------- > ---- > > b | character varying | | | | extended | > > | > > a | integer | | | | plain | > > | > > Partition of: rlp FOR VALUES FROM (15) TO (20) Partition constraint: > > ((a IS NOT NULL) AND (a >= 15) AND (a < 20)) Partition key: LIST (b > > varchar_ops) > > Partitions: rlp3abcd FOR VALUES IN ('ab', 'cd'), > > rlp3efgh FOR VALUES IN ('ef', 'gh'), > > rlp3nullxy FOR VALUES IN (NULL, 'xy'), > > rlp3_default DEFAULT > > > > postgres=# explain select * from rlp3 where a = 2; > > QUERY PLAN > > -------------------------------------------------------------------- > > Append (cost=0.00..103.62 rows=24 width=36) > > -> Seq Scan on rlp3abcd (cost=0.00..25.88 rows=6 width=36) > > Filter: (a = 2) > > -> Seq Scan on rlp3efgh (cost=0.00..25.88 rows=6 width=36) > > Filter: (a = 2) > > -> Seq Scan on rlp3nullxy (cost=0.00..25.88 rows=6 width=36) > > Filter: (a = 2) > > -> Seq Scan on rlp3_default (cost=0.00..25.88 rows=6 width=36) > > Filter: (a = 2) > > (9 rows) > > ----- > > This one too would be solved with the other patch I mentioned to fix > get_relation_info() to load the partition constraint so that constraint > exclusion can use it. > Partition in the earlier example given by Thibaut is a leaf partition, > whereas rlp3 above is a > sub-partitioned partition, but both are partitions nonetheless. > > Fixing partprune.c like we're doing with the > v2_ignore_contradictory_where_clauses_at_partprune_step.patch only works for > the latter, because only > partitioned tables visit partprune.c. > > OTOH, the other patch only applies to situations where constraint_exclusion = > on. > I see. I think that following example discussed in this thread before would also be solved with your patch, not v2_ignore_contradictory_where_clauses_at_partprune_step.patch.
postgres=# set constraint_exclusion to on;
postgres=# explain select * from test2_0_20 where id = 25;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
> > I think that the place of check contradiction process was wrong At
> > ignore_contradictory_where_clauses_at_partprune_step.patch.
> > So I fixed it.
>
> Thanks. Patch contains some whitespace noise:
>
> $ git diff --check
> src/backend/partitioning/partprune.c:790: trailing whitespace.
> + * given its partition constraint, we can ignore it,
> src/backend/partitioning/partprune.c:791: trailing whitespace.
> + * that is not try to pass it to the pruning code.
> src/backend/partitioning/partprune.c:792: trailing whitespace.
> + * We should do that especially to avoid pruning code
> src/backend/partitioning/partprune.c:810: trailing whitespace.
> +
> src/test/regress/sql/partition_prune.sql:87: trailing whitespace.
> +-- where clause contradicts sub-partition's constraint
>
> Can you please fix it?
>
Thanks for checking.
I'm attaching the latest patch.
>
> BTW, now I'm a bit puzzled between whether this case should be fixed by
> hacking on partprune.c like
> this patch does or whether to work on getting the other patch committed and
> expect users to set
> constraint_exclusion = on for this to behave as expected. The original
> intention of setting
> partition_qual in set_relation_partition_info() was for partprune.c to use it
> to remove useless
> arguments of OR clauses which otherwise would cause the failure to correctly
> prune the default partitions
> of sub-partitioned tables. As shown by the examples in this thread, the
> original effort was
> insufficient, which this patch aims to improve. But, it also expands the
> scope of partprune.c's usage
> of partition_qual, which is to effectively perform full-blown constraint
> exclusion without being
> controllable by constraint_exclusion GUC, which may be seen as being good or
> bad. The fact that it
> helps in getting partition pruning working correctly in more obscure cases
> like those discussed in
> this thread means it's good maybe.
>
Umm, even though this modification might be overhead, I think this problem
should be solved
without setting constraint_exclusion GUC. But I'm not sure.
Best regards,
Yuzuko Hosoya
v3_ignore_contradictory_where_clauses_at_partprune_step.patch
Description: Binary data
