Re: Problem with default partition pruning

On 2019/04/10 14:55, Amit Langote wrote:
> 2. Hosoya-san reported on 2019/03/22 that a contradictory WHERE clause
> applied to a *partition* doesn't return an empty plan:
>
>> I understood Amit's proposal. But I think the issue Thibaut reported
>> would occur regardless of whether clauses have OR clauses or not as
>> follows.
>>
>> I tested a query which should output "One-Time Filter: false".
>>
>> # explain select * from test2_0_20 where id = 25;
>> QUERY PLAN
>> -----------------------------------------------------------------------
>> Append (cost=0.00..25.91 rows=6 width=36)
>> -> Seq Scan on test2_10_20_def (cost=0.00..25.88 rows=6 width=36)
>> Filter: (id = 25)
>
> So, she proposed to apply predicate_refuted_by to the whole
> baserestrictinfo (at least in the latest patch), which is same as always
> performing constraint exclusion to sub-partitioned partitions. I
> initially thought it might be a good idea, but only later realized that
> now there will be two places doing the same constraint exclusion proof --
> gen_partprune_steps_internal(), and set_rel_size() calling
> relation_excluded_by_constraints(). The latter depends on
> constraint_exclusion GUC whose default being 'partition' would mean we'd
> not get an empty plan with it. Even if you turn it to 'on', a bug of
> get_relation_constraints() will prevent the partition constraint from
> being loaded and performing constraint exclusion with it; I reported it in
> [1].
>
> I think that we may be better off solving the latter problem as follows:
>
> 1. Modify relation_excluded_by_constraints() to *always* try to exclude
> "baserel" partitions using their partition constraint (disregarding
> constraint_exclusion = off/partition).
>
> 2. Modify prune_append_rel_partitions(), which runs much earlier these
> days compared to set_rel_size(), to call relation_excluded_by_constraint()
> modified as described in step 1. If it returns true, don't perform
> partition pruning, set the appendrel parent as dummy right away. It's not
> done today, but appendrel parent can also be set to dummy based on the
> result of pruning, that is, when get_matching_partitions() returns no
> matching partitions.
>
> 3. Modify set_base_rel_sizes() to ignore already-dummy rels, so that we
> don't perform constraint exclusion again via set_rel_size().
>
> I have to say this other problem involving partition constraints is quite
> complicated (aforementioned past bug messing up the situation further), so
> it would be nice if a committer can review and commit the solutions for
> the originally reported pruning issues.

Just to be clear, I wrote this for HEAD. In PG 11, set_rel_size() and
relation_excluded_by_constraints() run before
prune_append_rel_partitions(), so we won't need to change the latter when
back-patching.

Thanks,
Amit