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 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.

Thanks,
Amit