Re: Problem with default partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Yuzuko Hosoya <hosoya(dot)yuzuko(at)lab(dot)ntt(dot)co(dot)jp>, "'Thibaut'" <thibaut(dot)madelaine(at)dalibo(dot)com>, "'Imai, Yoshikazu'" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
Cc: "'PostgreSQL Hackers'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Problem with default partition pruning
Date: 2019-04-05 09:46:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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;
> --------------------------------------------------------------------
> 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 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?

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.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2019-04-05 10:07:17 Re: speeding up planning with partitions
Previous Message Floris Van Nee 2019-04-05 09:13:29 Re: speeding up planning with partitions