Re: Problem with default partition pruning - Mailing list pgsql-hackers

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