Partitions aggregation

[Krysztophe]

We have more an d more plans with many partitions that make unreadable plans

I'd like pev2 to « aggregate » the nodes of the partitions.
Of course, all partitions do not have the same plans.

I'm note sure that detecting the Append nodes should be enough.

Te user should be alble to partition this « partition aware » mode with a checkbox.

Some simple use cases:

--  pgbench -i -s 100 --partitions=100 
SET max_parallel_workers_per_gather TO 0 ;

-- https://explain.dalibo.com/plan/gggcb54d3a04d505
EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
SELECT * FROM pgbench_accounts
WHERE aid +0  = 888 ;

-- https://explain.dalibo.com/plan/3179a1834085acg1
EXPLAIN (ANALYZE,BUFFERS)
SELECT SUM(aid) FROM pgbench_accounts
WHERE aid +0  = 888 ;

--- With  VERBOSE :
-- https://explain.dalibo.com/plan/4b8e8d1bhg565gc5 


SET max_parallel_workers_per_gather TO 2 ;

-- https://explain.dalibo.com/plan/acb7c49587899129
EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
SELECT * FROM pgbench_accounts
WHERE aid +0  = 888 ;

---- To have different plans on the partitions
CREATE INDEX ON pgbench_accounts_6 ((aid+0)) ;
CREATE INDEX ON pgbench_accounts_8 ((aid+0)) ;

SET max_parallel_workers_per_gather TO 0 ;

-- https://explain.dalibo.com/plan/9eb585d3ch3914g1
EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
SELECT * FROM pgbench_accounts
WHERE aid +0  = 888 ;

VACUUM ANALYZE ;

-- https://explain.dalibo.com/plan/0ha3gedh3h21761f
EXPLAIN (ANALYZE,BUFFERS,VERBOSE)

SELECT * FROM pgbench_accounts
WHERE aid +0  = 888 ;

-- https://explain.dalibo.com/plan/g6f2ga726c9d38cf
SET max_parallel_workers_per_gather TO 0 ;
EXPLAIN (ANALYZE,BUFFERS)
SELECT SUM(aid) FROM pgbench_accounts
WHERE aid +0  = 888 ;

[Krysztophe]

Another nice case: https://explain.dalibo.com/plan/359419g3b3678db9

[Krysztophe]

I wanted to add an idea for basic cases first and some examples, but it becomes complicated very quickly.

• Detecting the parallelism is already complicated:

  • In the basic case we have a Append (or other) and "basic" nodes below, we could imagine detecting it easily.
  • It becomes quickly complicated, eg with parallelism
  • It is much more complicated when there are one (or more ?) node(s) beween the Append/Gather and the nodes on the partitions (see this). Finalize and Partialkeywords could help.
  • And you can have two levels of partitioning (like in join between 2 parallel tables), or sub-partitioning example 1, example 2

• The goal is to reduce the width of the plan in the window to make the plan readable, so it leaves 2 ways:

  • put partitions one below the other instead of side-by-side : not possible when you have more than level.
  • "condense" nodes into a single node : I don't believe in a simple way to do this for partition nodes. They are not similar, like Parallel nodes : the access path of a partition may not the same of its sister partition (like https://explain.dalibo.com/plan/47bc1a3g4f9f2b06#plan/node/17 vs https://explain.dalibo.com/plan/47bc1a3g4f9f2b06#plan/node/19). So you can have different complicated trees for each partition

So, for me, condensing/compacting the partitions becomes a more general question:
condensing any node, and the others below, into a single node
(with a click or automatically).
The partitions would just be special case. In a more general way, that would help for huge plans.

I imagine that this is for another issue, and I'm astonished that it was not already discussed or asked. Shall I open it?