Re: plan shape work - Mailing list pgsql-hackers

Hi there,

I've tried v10-000{1,2}+v9-0002 and v9-000{1,2}. I was curious whether
the names choose_plan_name() chose for subqueries match the Subquery
Scan names in the EXPLAIN plan. My guess is that since the former is
chosen before planning and the latter after planning, they might
differ. I think it's probably ok to have different naming mechanisms
as long as the names are unique within themselves. But in case anyone
else cares about the naming inconsistency, here's an example that
shows it.

-- applied patches
I've applied v10-0001, v10-0002 and v9-0002. I needed v9-0002 because
I want to see the plan_names in the debug plan and in the
EXPLAIN(RANGE_TABLE) plan with pg_overexplain.

(Applying v9-000{1,2} instead should give the same results)

-- setup
CREATE TABLE r (a int, b int);
CREATE TABLE s (c int, d int);
LOAD 'pg_overexplain';
SET debug_print_plan to on;
SET client_min_messages to 'log';

-- query
EXPLAIN (range_table, costs off)
SELECT *
FROM
  (SELECT a FROM
     (SELECT a, b FROM r WHERE b > 42 ORDER BY a)
   UNION ALL
     (SELECT c FROM
(SELECT c, d FROM s WHERE d > 24 ORDER BY d)));

-- plan
                  QUERY PLAN
----------------------------------------------
 Append
   Append RTIs: 1
   ->  Subquery Scan on unnamed_subquery_1
         Scan RTI: 4
         ->  Sort
               Sort Key: r.a
               ->  Seq Scan on r
                     Filter: (b > 42)
                     Scan RTI: 6
   ->  Subquery Scan on unnamed_subquery_2
         Scan RTI: 5
         ->  Sort
               Sort Key: s.d
               ->  Seq Scan on s
                     Filter: (d > 24)
                     Scan RTI: 7
 RTI 1 (subquery, inherited, in-from-clause):
   Eref: unnamed_subquery (a)
 RTI 2 (subquery):
   Eref: unnamed_subquery (a)
 RTI 3 (subquery):
   Eref: unnamed_subquery (c)
 RTI 4 (subquery, in-from-clause):
   Eref: unnamed_subquery (a, b)
 RTI 5 (subquery, in-from-clause):
   Eref: unnamed_subquery (c, d)
 RTI 6 (relation, in-from-clause):
   Subplan: unnamed_subquery
   Eref: r (a, b)
   Relation: r
   Relation Kind: relation
   Relation Lock Mode: AccessShareLock
   Permission Info Index: 1
 RTI 7 (relation, in-from-clause):
   Subplan: unnamed_subquery_1
   Eref: s (c, d)
   Relation: s
   Relation Kind: relation
   Relation Lock Mode: AccessShareLock
   Permission Info Index: 2
 Unprunable RTIs: 6 7
(41 rows)

-- interesting part of the debug plan:
   :subplans <>
   :subrtinfos (
      {SUBPLANRTINFO
      :plan_name unnamed_subquery
      :rtoffset 5
      :dummy false
      }
      {SUBPLANRTINFO
      :plan_name unnamed_subquery_1
      :rtoffset 6
      :dummy false
      }

 

It appears that in the EXPLAIN plan the subqueries are named
"unnamed_subquery" (does not show up in the EXPLAIN output),
"unnamed_subquery_1", and "unnamed_subquery_2"; whereas in the RTIs
section from pg_overexplain, as well as in the debug plan's
:subrtinfos section, the subplans are named "unnamed_subquery" and
"unnamed_subquery_1".

IIUC, the Subquery Scan names in the query plan, for example:
   ->  Subquery Scan on unnamed_subquery_2
is the name assigned to this Subquery Scan node of RTI: 5, after
planning.

And the Subplan name of an RTI with pg_overexplain, for example:
 RTI 7 (relation, in-from-clause):
   Subplan: unnamed_subquery_1
   Eref: s (c, d)
is the Subplan name chosen before planning. RTI 7 here maps to the
SUBPLANRTINFO with ":rtoffset 6" in the debug plan, which means it
belongs to the Subplan named "unnamed_subquery_1". This is what I
think causes confusion, because from the query plan we see that RTI 7
is under the Subquery Scan on "unnamed_subquery_2", not
"unnamed_subquery_1".

I think technically this is not a problem, since we can uniquely
identify the Subplans using the names assigned before planning, and we
can also uniquely identify the Subquery Scans in the EXPLAIN plan
using the names assigned after planning. Still, I found it a bit
confusing when looking at the EXPLAIN(RANGE_TABLE) output, where the
same name "unnamed_subquery_1" not only doesn't mean the same plan
node, but also not in the same branch of the plan tree.

Thoughts?

Best,

Alex