On 11 May 2018 at 08:05, Robert Haas <robertmh...@gmail.com> wrote: > > On Thu, May 10, 2018 at 3:45 PM, Alvaro Herrera > <alvhe...@2ndquadrant.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
