Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit fd0398f

Browse filesBrowse files
tglsfdctglsfdc
committed
Improve EXPLAIN's display of SubPlan nodes and output parameters.
Historically we've printed SubPlan expression nodes as "(SubPlan N)", which is pretty uninformative. Trying to reproduce the original SQL for the subquery is still as impractical as before, and would be mighty verbose as well. However, we can still do better than that. Displaying the "testexpr" when present, and adding a keyword to indicate the SubLinkType, goes a long way toward showing what's really going on. In addition, this patch gets rid of EXPLAIN's use of "$n" to represent subplan and initplan output Params. Instead we now print "(SubPlan N).colX" or "(InitPlan N).colX" to represent the X'th output column of that subplan. This eliminates confusion with the use of "$n" to represent PARAM_EXTERN Params, and it's useful for the first part of this change because it eliminates needing some other indication of which subplan is referenced by a SubPlan that has a testexpr. In passing, this adds simple regression test coverage of the ROWCOMPARE_SUBLINK code paths, which were entirely unburdened by testing before. Tom Lane and Dean Rasheed, reviewed by Aleksander Alekseev. Thanks to Chantal Keller for raising the question of whether this area couldn't be improved. Discussion: https://postgr.es/m/2838538.1705692747@sss.pgh.pa.us
1 parent cc4826d commit fd0398f
Copy full SHA for fd0398f
Expand file treeCollapse file tree

24 files changed

+793
-464
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/expected/postgres_fdw.out
+17-17Lines changed: 17 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -3062,10 +3062,10 @@ select exists(select 1 from pg_enum), sum(c1) from ft1;
30623062
QUERY PLAN
30633063
--------------------------------------------------
30643064
Foreign Scan
3065-
Output: $0, (sum(ft1.c1))
3065+
Output: (InitPlan 1).col1, (sum(ft1.c1))
30663066
Relations: Aggregate on (public.ft1)
30673067
Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1"
3068-
InitPlan 1 (returns $0)
3068+
InitPlan 1
30693069
-> Seq Scan on pg_catalog.pg_enum
30703070
(6 rows)
30713071

@@ -3080,8 +3080,8 @@ select exists(select 1 from pg_enum), sum(c1) from ft1 group by 1;
30803080
QUERY PLAN
30813081
---------------------------------------------------
30823082
GroupAggregate
3083-
Output: $0, sum(ft1.c1)
3084-
InitPlan 1 (returns $0)
3083+
Output: (InitPlan 1).col1, sum(ft1.c1)
3084+
InitPlan 1
30853085
-> Seq Scan on pg_catalog.pg_enum
30863086
-> Foreign Scan on public.ft1
30873087
Output: ft1.c1
@@ -3305,10 +3305,10 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord
33053305

33063306
explain (verbose, costs off)
33073307
select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
3308-
QUERY PLAN
3309-
-------------------------------------------------------------------
3308+
QUERY PLAN
3309+
-------------------------------------------------------------------------------
33103310
Aggregate
3311-
Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1))
3311+
Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan 1).col1)))
33123312
-> Foreign Scan on public.ft1
33133313
Output: ft1.c2
33143314
Remote SQL: SELECT c2 FROM "S 1"."T 1"
@@ -6171,9 +6171,9 @@ UPDATE ft2 AS target SET (c2, c7) = (
61716171
Update on public.ft2 target
61726172
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1
61736173
-> Foreign Scan on public.ft2 target
6174-
Output: $1, $2, (SubPlan 1 (returns $1,$2)), target.ctid, target.*
6174+
Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), target.ctid, target.*
61756175
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE
6176-
SubPlan 1 (returns $1,$2)
6176+
SubPlan 1
61776177
-> Foreign Scan on public.ft2 src
61786178
Output: (src.c2 * 10), src.c7
61796179
Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
@@ -11685,9 +11685,9 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
1168511685
QUERY PLAN
1168611686
----------------------------------------------------------------------------------------
1168711687
Nested Loop Left Join
11688-
Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0)
11688+
Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPlan 1).col1)
1168911689
Join Filter: (t1.a = async_pt.a)
11690-
InitPlan 1 (returns $0)
11690+
InitPlan 1
1169111691
-> Aggregate
1169211692
Output: count(*)
1169311693
-> Append
@@ -11699,10 +11699,10 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
1169911699
Output: t1.a, t1.b, t1.c
1170011700
-> Append
1170111701
-> Async Foreign Scan on public.async_p1 async_pt_1
11702-
Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, $0
11702+
Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, (InitPlan 1).col1
1170311703
Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000))
1170411704
-> Async Foreign Scan on public.async_p2 async_pt_2
11705-
Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, $0
11705+
Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, (InitPlan 1).col1
1170611706
Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000))
1170711707
(20 rows)
1170811708

@@ -11713,7 +11713,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt W
1171311713
Nested Loop Left Join (actual rows=1 loops=1)
1171411714
Join Filter: (t1.a = async_pt.a)
1171511715
Rows Removed by Join Filter: 399
11716-
InitPlan 1 (returns $0)
11716+
InitPlan 1
1171711717
-> Aggregate (actual rows=1 loops=1)
1171811718
-> Append (actual rows=400 loops=1)
1171911719
-> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)
@@ -11936,11 +11936,11 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl)
1193611936
SERVER loopback OPTIONS (table_name 'base_tbl');
1193711937
EXPLAIN (VERBOSE, COSTS OFF)
1193811938
SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl);
11939-
QUERY PLAN
11940-
-----------------------------------------------------------------------------
11939+
QUERY PLAN
11940+
---------------------------------------------------------------------------------------------------------------
1194111941
Seq Scan on public.base_tbl
1194211942
Output: base_tbl.a
11943-
Filter: (SubPlan 1)
11943+
Filter: (ANY ((base_tbl.a = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2)))
1194411944
SubPlan 1
1194511945
-> Result
1194611946
Output: base_tbl.a, (random() > '0'::double precision)

‎doc/src/sgml/perform.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/perform.sgml
+100-2Lines changed: 100 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -573,8 +573,106 @@ WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2;
573573
which shows that the planner thinks that sorting <literal>onek</literal> by
574574
index-scanning is about 12% more expensive than sequential-scan-and-sort.
575575
Of course, the next question is whether it's right about that.
576-
We can investigate that using <command>EXPLAIN ANALYZE</command>, as discussed
577-
below.
576+
We can investigate that using <command>EXPLAIN ANALYZE</command>, as
577+
discussed <link linkend="using-explain-analyze">below</link>.
578+
</para>
579+
580+
<para>
581+
<indexterm>
582+
<primary>subplan</primary>
583+
</indexterm>
584+
Some query plans involve <firstterm>subplans</firstterm>, which arise
585+
from sub-<literal>SELECT</literal>s in the original query. Such
586+
queries can sometimes be transformed into ordinary join plans, but
587+
when they cannot be, we get plans like:
588+
589+
<screen>
590+
EXPLAIN VERBOSE SELECT unique1
591+
FROM tenk1 t
592+
WHERE t.ten &lt; ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);
593+
594+
QUERY PLAN
595+
-------------------------------------------------------------------&zwsp;------
596+
Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4)
597+
Output: t.unique1
598+
Filter: (ALL (t.ten &lt; (SubPlan 1).col1))
599+
SubPlan 1
600+
-&gt; Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4)
601+
Output: o.ten
602+
Filter: (o.four = t.four)
603+
</screen>
604+
605+
This rather artificial example serves to illustrate a couple of
606+
points: values from the outer plan level can be passed down into a
607+
subplan (here, <literal>t.four</literal> is passed down) and the
608+
results of the sub-select are available to the outer plan. Those
609+
result values are shown by <command>EXPLAIN</command> with notations
610+
like
611+
<literal>(<replaceable>subplan_name</replaceable>).col<replaceable>N</replaceable></literal>,
612+
which refers to the <replaceable>N</replaceable>'th output column of
613+
the sub-<literal>SELECT</literal>.
614+
</para>
615+
616+
<para>
617+
<indexterm>
618+
<primary>subplan</primary>
619+
<secondary>hashed</secondary>
620+
</indexterm>
621+
In the example above, the <literal>ALL</literal> operator runs the
622+
subplan again for each row of the outer query (which accounts for the
623+
high estimated cost). Some queries can use a <firstterm>hashed
624+
subplan</firstterm> to avoid that:
625+
626+
<screen>
627+
EXPLAIN SELECT *
628+
FROM tenk1 t
629+
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);
630+
631+
QUERY PLAN
632+
-------------------------------------------------------------------&zwsp;-------------------------
633+
Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244)
634+
Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
635+
SubPlan 1
636+
-&gt; Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4)
637+
(4 rows)
638+
</screen>
639+
640+
Here, the subplan is run a single time and its output is loaded into
641+
an in-memory hash table, which is then probed by the
642+
outer <literal>ANY</literal> operator. This requires that the
643+
sub-<literal>SELECT</literal> not reference any variables of the outer
644+
query, and that the <literal>ANY</literal>'s comparison operator be
645+
amenable to hashing.
646+
</para>
647+
648+
<para>
649+
<indexterm>
650+
<primary>initplan</primary>
651+
</indexterm>
652+
If, in addition to not referencing any variables of the outer query,
653+
the sub-<literal>SELECT</literal> cannot return more than one row,
654+
it may instead be implemented as an <firstterm>initplan</firstterm>:
655+
656+
<screen>
657+
EXPLAIN VERBOSE SELECT unique1
658+
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
659+
660+
QUERY PLAN
661+
------------------------------------------------------------&zwsp;--------
662+
Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)
663+
Output: t1.unique1
664+
Filter: (t1.ten = (InitPlan 1).col1)
665+
InitPlan 1
666+
-&gt; Result (cost=0.00..0.02 rows=1 width=4)
667+
Output: ((random() * '10'::double precision))::integer
668+
</screen>
669+
670+
An initplan is run only once per execution of the outer plan, and its
671+
results are saved for re-use in later rows of the outer plan. So in
672+
this example <literal>random()</literal> is evaluated only once and
673+
all the values of <literal>t1.ten</literal> are compared to the same
674+
randomly-chosen integer. That's quite different from what would
675+
happen without the sub-<literal>SELECT</literal> construct.
578676
</para>
579677

580678
</sect2>

‎src/backend/commands/explain.c

Copy file name to clipboardExpand all lines: src/backend/commands/explain.c
-32Lines changed: 0 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,6 @@ static void show_tidbitmap_info(BitmapHeapScanState *planstate,
116116
static void show_instrumentation_count(const char *qlabel, int which,
117117
PlanState *planstate, ExplainState *es);
118118
static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es);
119-
static void show_eval_params(Bitmapset *bms_params, ExplainState *es);
120119
static const char *explain_get_index_name(Oid indexId);
121120
static bool peek_buffer_usage(ExplainState *es, const BufferUsage *usage);
122121
static void show_buffer_usage(ExplainState *es, const BufferUsage *usage);
@@ -1914,10 +1913,6 @@ ExplainNode(PlanState *planstate, List *ancestors,
19141913
ExplainPropertyInteger("Workers Planned", NULL,
19151914
gather->num_workers, es);
19161915

1917-
/* Show params evaluated at gather node */
1918-
if (gather->initParam)
1919-
show_eval_params(gather->initParam, es);
1920-
19211916
if (es->analyze)
19221917
{
19231918
int nworkers;
@@ -1942,10 +1937,6 @@ ExplainNode(PlanState *planstate, List *ancestors,
19421937
ExplainPropertyInteger("Workers Planned", NULL,
19431938
gm->num_workers, es);
19441939

1945-
/* Show params evaluated at gather-merge node */
1946-
if (gm->initParam)
1947-
show_eval_params(gm->initParam, es);
1948-
19491940
if (es->analyze)
19501941
{
19511942
int nworkers;
@@ -3550,29 +3541,6 @@ show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es)
35503541
}
35513542
}
35523543

3553-
/*
3554-
* Show initplan params evaluated at Gather or Gather Merge node.
3555-
*/
3556-
static void
3557-
show_eval_params(Bitmapset *bms_params, ExplainState *es)
3558-
{
3559-
int paramid = -1;
3560-
List *params = NIL;
3561-
3562-
Assert(bms_params);
3563-
3564-
while ((paramid = bms_next_member(bms_params, paramid)) >= 0)
3565-
{
3566-
char param[32];
3567-
3568-
snprintf(param, sizeof(param), "$%d", paramid);
3569-
params = lappend(params, pstrdup(param));
3570-
}
3571-
3572-
if (params)
3573-
ExplainPropertyList("Params Evaluated", params, es);
3574-
}
3575-
35763544
/*
35773545
* Fetch the name of an index in an EXPLAIN
35783546
*

‎src/backend/optimizer/plan/subselect.c

Copy file name to clipboardExpand all lines: src/backend/optimizer/plan/subselect.c
+4-18Lines changed: 4 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -560,22 +560,9 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
560560
splan->plan_id);
561561

562562
/* Label the subplan for EXPLAIN purposes */
563-
splan->plan_name = palloc(32 + 12 * list_length(splan->setParam));
564-
sprintf(splan->plan_name, "%s %d",
565-
isInitPlan ? "InitPlan" : "SubPlan",
566-
splan->plan_id);
567-
if (splan->setParam)
568-
{
569-
char *ptr = splan->plan_name + strlen(splan->plan_name);
570-
571-
ptr += sprintf(ptr, " (returns ");
572-
foreach(lc, splan->setParam)
573-
{
574-
ptr += sprintf(ptr, "$%d%s",
575-
lfirst_int(lc),
576-
lnext(splan->setParam, lc) ? "," : ")");
577-
}
578-
}
563+
splan->plan_name = psprintf("%s %d",
564+
isInitPlan ? "InitPlan" : "SubPlan",
565+
splan->plan_id);
579566

580567
/* Lastly, fill in the cost estimates for use later */
581568
cost_subplan(root, splan, plan);
@@ -3047,8 +3034,7 @@ SS_make_initplan_from_plan(PlannerInfo *root,
30473034
node = makeNode(SubPlan);
30483035
node->subLinkType = EXPR_SUBLINK;
30493036
node->plan_id = list_length(root->glob->subplans);
3050-
node->plan_name = psprintf("InitPlan %d (returns $%d)",
3051-
node->plan_id, prm->paramid);
3037+
node->plan_name = psprintf("InitPlan %d", node->plan_id);
30523038
get_first_col_type(plan, &node->firstColType, &node->firstColTypmod,
30533039
&node->firstColCollation);
30543040
node->parallel_safe = plan->parallel_safe;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.