Re: Problem with default partition pruning

Hosoya-san,

Thanks for updating the patches.

I have no comment in particular about
v4_default_partition_pruning.patch, but let me reiterate my position
about v5_ignore_contradictory_where_clauses_at_partprune_step.patch,
which I first stated in the following email a few months ago:

https://www.postgresql.org/message-id/d2c38e4e-ade4-74de-f686-af37e4a5f1c1%40lab.ntt.co.jp

This patch proposes to apply constraint exclusion to check whether it
will be wasteful to generate pruning steps from a given clause against
a given sub-partitioned table, because the clause contradicts its
partition clause. Actually, the patch started out to generalize the
existing usage of constraint exclusion in partprune.c that's used to
skip processing useless arguments of an OR clause. The problem with
steps generated from such contradictory clauses is that they fail to
prune the default partition of a sub-partitioned table, because the
value extracted from such a clause appears to the pruning logic to
fall in the default partition, given that the pruning logic proper is
unaware of the partition constraint of the partitioned table that
pruning is being applied to. Here is an example similar to one that
Hosoya-san shared earlier on this thread that shows the problem.

create table p (a int) partition by range (a);
create table p1 partition of p for values from (0) to (20) partition
by range (a);
create table p11 partition of p1 for values from (0) to (10);
create table p1_def partition of p1 default;
-- p11 correctly pruned, but p1_def not
explain select * from p1 where a = 25;
QUERY PLAN
──────────────────────────────────────────────────────────────
Append (cost=0.00..41.94 rows=13 width=4)
-> Seq Scan on p1_def (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 25)
(3 rows)

Here without the knowledge that p1's range is restricted to 0 <= a <
20 by way of its partition constraint, the pruning logic, when handed
the value 25, concludes that p1_def must be scanned. With the patch,
partprune.c concludes without performing pruning that scanning any of
p1's partitions is unnecessary.

explain select * from p1 where a = 25;
QUERY PLAN
──────────────────────────────────────────
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

Actually, as of 11.4, setting constraint_exclusion = on, by way of
relation_excluded_by_constraints(), will give you the same result even
without the patch. My argument earlier was that we shouldn't have two
places that will do essentially the same processing -- partprune.c
with the patch applied and relation_excluded_by_constraints(). That
is, we should only keep the latter, with the trade-off that users have
to live with the default partition of sub-partitioned tables not being
pruned in some corner cases like this one.

Note that there's still a problem with the existing usage of
constraint exclusion in partprune.c, which Thibaut first reported on
this thread [1].

explain select * from p1 where a = 25 or a = 5;
QUERY PLAN
──────────────────────────────────────────────────────────────
Append (cost=0.00..96.75 rows=50 width=4)
-> Seq Scan on p11 (cost=0.00..48.25 rows=25 width=4)
Filter: ((a = 25) OR (a = 5))
-> Seq Scan on p1_def (cost=0.00..48.25 rows=25 width=4)
Filter: ((a = 25) OR (a = 5))
(5 rows)

Here only one of the OR's arguments can be true for p1's partitions,
but partprune.c's current usage of constraint exclusion fails to
notice that. I had posted a patch [2] to solve this specific problem.
Hosoya-san's patch is a generalization of my patch.

Thanks,
Amit

[1] https://www.postgresql.org/message-id/bd03f475-30d4-c4d0-3d7f-d2fbde755971%40dalibo.com

[2] https://www.postgresql.org/message-id/9bb31dfe-b0d0-53f3-3ea6-e64b811424cf%40lab.ntt.co.jp