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

On 11 May 2018 at 08:05, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Thu, May 10, 2018 at 3:45 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > The examples for runtime pruning are lame -- in the first, the text says
> > "watch out for Subplans Removed" and then the example provided doesn't
> > show one. (That example is probably exercising the wrong thing.)
>
> It seems to me that EXPLAIN output should have a clear way to show --
> and to distinguish -- (1) plan-time pruning, (2) executor startup time
> pruning, (3) mid-execution pruning. I don't think that's entirely the
> case right now.

I'm open to improving this, but I've just not come up with any bright
ideas on how to, yet.

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.

Changing parameters may cause some nodes to be scanned fewer times
than other nodes. The "nloops" count being lower than the nloop count
of the Append indicates this. e.g nloops=5 on an Append subnode vs
nloops=8 on the Append node indicates the node was eliminated 3 times.

Although complications around Parallel Append could make it quite
difficult to count nloops, since a node running a partial plan could
be executed by may workers which would increase the nloops.

Solutions?

The best I can think of right now is to add 2 more int properties to
the EXPLAIN output:

1. Subplans removed by plan-time constraints exclusion: N
2. Subplans removed by plan-time partition pruning: N

The rename the "Subplans Removed" that's there today to "Subplans
removed by run-time pruning"

These names are not very good, also. I'm also not very excited about
adding this. This also does nothing for phase 3.

Would something like that address your concern? Or do you have another idea?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services