Re: Should we add GUCs to allow partition pruning to be disabled?

On Thu, May 10, 2018 at 10:22 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> Here's a recap of the current way to determine where the pruning occurred:
>
> Phase 1: Plan time pruning:
>
> EXPLAIN/EXPLAIN ANALYZE shows Append/MergeAppend/ModifyTable shows
> fewer subnodes than there are partitions.
> Both EXPLAIN and EXPLAIN ANALYZE output gives no indication of any pruning.
>
> Phase 2: Executor init pruning:
>
> EXPLAIN and EXPLAIN ANALYZE shows Append with fewer subnodes than
> there are partitions + "Subplans Removed: <N>" appears to indicate the
> number of subnodes removed by this phase.
>
> MergeAppend and ModifyTable are unsupported in PG11.
>
> Phase 3: Executor run pruning:
>
> EXPLAIN/EXPLAIN ANALYZE shows all nodes that survived phase 1+2.
>
> EXPLAIN ANALYZE shows that if a given node was never executed then the
> runtime times appear as "(never executed)". If the Append was executed
> and a subnode the Append was "(never executed)" then it was pruned by
> this phase.

Hmm, that's actually not as bad as I thought. Thanks for the
explanation. I think if I were going to try to improve things, I'd
try to annotate the Append node with the name of the partitioned table
that it's using for pruning in case #2 and case #3, and maybe
something to indicate which type of pruning is in use. That would
make it really clear whether pruning is enabled or not. The methods
you mention above sort of require reading the tea leaves -- and it
might not always be very easy to distinguish between cases where
pruning is possible but nothing got pruned (imagine an inequality
qual) and where it's not even possible in the first place.

e.g.

Append
Execution-Time Pruning: order_lines (at executor startup)
-> Index Scan ...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company