Re: Proposal: add a debug message about using geqo

I agree this should be part of explain output.

I would not print the current value of geqo_threshold and leave setting display the exclusive purview of the settings option.

The presentation of only a single geqo result seems incorrect given that multiple trees can exist.  In the first example below the full outer join causes 3 relations to be seen as a single relation at the top level (hence max join nodes = 4) while in the inner join case we see all 6 join nodes.  There should be two outputs of GEQO in the first explain, one with join nodes of 3 and the existing one with 4.

I also don't see the point of labelling them "max"; "join nodes" seems sufficient.

While it can probably be figured out from the rest of the plan, listing the names of the join nodes may be useful (and give join nodes some company).

David J.

postgres=# explain (verbose, geqo) with gs2 (v2) as materialized ( select * from generate_series(1,1) ) select * from gs2 as gs4 full outer join (select gs2a.v2 from gs2 as gs2a, gs2 as gs2b) as gs5 using (v2), generate_series(1, 1) as gs (v1) cross join gs2 as gs3 where v1 IN (select v2 from gs2);
                                             QUERY PLAN                                            
----------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.07..0.21 rows=1 width=12)
   Output: COALESCE(gs4.v2, gs2a.v2), gs.v1, gs3.v2
   CTE gs2
     ->  Function Scan on pg_catalog.generate_series  (cost=0.00..0.01 rows=1 width=4)
           Output: generate_series.generate_series
           Function Call: generate_series(1, 1)
   ->  Nested Loop  (cost=0.06..0.16 rows=1 width=12)
         Output: gs.v1, gs4.v2, gs2a.v2
         ->  Nested Loop  (cost=0.02..0.06 rows=1 width=4)
               Output: gs.v1
               Join Filter: (gs.v1 = gs2.v2)
               ->  Function Scan on pg_catalog.generate_series gs  (cost=0.00..0.01 rows=1 width=4)
                     Output: gs.v1
                     Function Call: generate_series(1, 1)
               ->  HashAggregate  (cost=0.02..0.03 rows=1 width=4)
                     Output: gs2.v2
                     Group Key: gs2.v2
                     ->  CTE Scan on gs2  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs2.v2
         ->  Hash Full Join  (cost=0.03..0.10 rows=1 width=8)
               Output: gs4.v2, gs2a.v2
               Hash Cond: (gs2a.v2 = gs4.v2)
               ->  Nested Loop  (cost=0.00..0.05 rows=1 width=4)
                     Output: gs2a.v2
                     ->  CTE Scan on gs2 gs2b  (cost=0.00..0.02 rows=1 width=0)
                           Output: gs2b.v2
                     ->  CTE Scan on gs2 gs2a  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs2a.v2
               ->  Hash  (cost=0.02..0.02 rows=1 width=4)
                     Output: gs4.v2
                     ->  CTE Scan on gs2 gs4  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs4.v2
   ->  CTE Scan on gs2 gs3  (cost=0.00..0.02 rows=1 width=4)
         Output: gs3.v2
 GeqoDetails: GEQO: used, geqo_threshold: 2, Max join nodes: 4
(35 rows)

postgres=# explain (verbose, geqo) with gs2 (v2) as materialized ( select * from generate_series(1,1) ) select * from gs2 as gs4 join (select gs2a.v2 from gs2 as gs2a, gs2 as gs2b) as gs5 using (v2), generate_series(1, 1) as gs (v1) cross join gs2 as gs3 where v1 IN (select v2 from gs2);
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.02..0.18 rows=1 width=12)
   Output: gs4.v2, gs.v1, gs3.v2
   CTE gs2
     ->  Function Scan on pg_catalog.generate_series  (cost=0.00..0.01 rows=1 width=4)
           Output: generate_series.generate_series
           Function Call: generate_series(1, 1)
   ->  Nested Loop  (cost=0.00..0.14 rows=1 width=12)
         Output: gs.v1, gs4.v2, gs3.v2
         ->  Nested Loop  (cost=0.00..0.11 rows=1 width=8)
               Output: gs.v1, gs4.v2
               ->  Nested Loop Semi Join  (cost=0.00..0.04 rows=1 width=4)
                     Output: gs.v1
                     Join Filter: (gs.v1 = gs2.v2)
                     ->  Function Scan on pg_catalog.generate_series gs  (cost=0.00..0.01 rows=1 width=4)
                           Output: gs.v1
                           Function Call: generate_series(1, 1)
                     ->  CTE Scan on gs2  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs2.v2
               ->  Nested Loop  (cost=0.00..0.05 rows=1 width=4)
                     Output: gs4.v2
                     Join Filter: (gs2a.v2 = gs4.v2)
                     ->  CTE Scan on gs2 gs2a  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs2a.v2
                     ->  CTE Scan on gs2 gs4  (cost=0.00..0.02 rows=1 width=4)
                           Output: gs4.v2
         ->  CTE Scan on gs2 gs3  (cost=0.00..0.02 rows=1 width=4)
               Output: gs3.v2
   ->  CTE Scan on gs2 gs2b  (cost=0.00..0.02 rows=1 width=0)
         Output: gs2b.v2
 GeqoDetails: GEQO: used, geqo_threshold: 2, Max join nodes: 6
(30 rows)