QUERY PLAN

--------------------------------------------------

Foreign Scan

Output:$0, (sum(ft1.c1))

Output:(InitPlan 1).col1, (sum(ft1.c1))

Relations: Aggregate on (public.ft1)

Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1"

InitPlan 1 (returns $0)

InitPlan 1

-> Seq Scan on pg_catalog.pg_enum

(6 rows)

QUERY PLAN

---------------------------------------------------

GroupAggregate

Output:$0, sum(ft1.c1)

InitPlan 1 (returns $0)

Output:(InitPlan 1).col1, sum(ft1.c1)

InitPlan 1

-> Seq Scan on pg_catalog.pg_enum

-> Foreign Scan on public.ft1

Output: ft1.c1

explain (verbose, costs off)

select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;

QUERY PLAN

-------------------------------------------------------------------

QUERY PLAN

-------------------------------------------------------------------------------

Aggregate

Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1))

Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = (hashed SubPlan 1).col1)))

-> Foreign Scan on public.ft1

Output: ft1.c2

Remote SQL: SELECT c2 FROM "S 1"."T 1"

Update on public.ft2 target

Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c7 = $3 WHERE ctid = $1

-> Foreign Scan on public.ft2 target

Output:$1, $2,(SubPlan 1 (returns $1,$2)), target.ctid, target.*

Output:(SubPlan 1).col1,(SubPlan 1).col2, (rescan SubPlan 1), target.ctid, target.*

Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 1100)) FOR UPDATE

SubPlan 1 (returns $1,$2)

SubPlan 1

-> Foreign Scan on public.ft2 src

Output: (src.c2 * 10), src.c7

Remote SQL: SELECT c2, c7 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))

QUERY PLAN

----------------------------------------------------------------------------------------

Nested Loop Left Join

Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0)

Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ((InitPlan 1).col1)

Join Filter: (t1.a = async_pt.a)

InitPlan 1 (returns $0)

InitPlan 1

-> Aggregate

Output: count(*)

-> Append

Output: t1.a, t1.b, t1.c

-> Append

-> Async Foreign Scan on public.async_p1 async_pt_1

Output: async_pt_1.a, async_pt_1.b, async_pt_1.c,$0

Output: async_pt_1.a, async_pt_1.b, async_pt_1.c,(InitPlan 1).col1

Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000))

-> Async Foreign Scan on public.async_p2 async_pt_2

Output: async_pt_2.a, async_pt_2.b, async_pt_2.c,$0

Output: async_pt_2.a, async_pt_2.b, async_pt_2.c,(InitPlan 1).col1

Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000))

(20 rows)

Nested Loop Left Join (actual rows=1 loops=1)

Join Filter: (t1.a = async_pt.a)

Rows Removed by Join Filter: 399

InitPlan 1 (returns $0)

InitPlan 1

-> Aggregate (actual rows=1 loops=1)

-> Append (actual rows=400 loops=1)

-> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)

SERVER loopback OPTIONS (table_name 'base_tbl');

EXPLAIN (VERBOSE, COSTS OFF)

SELECT a FROM base_tbl WHERE (a, random() > 0) IN (SELECT a, random() > 0 FROM foreign_tbl);

QUERY PLAN

-----------------------------------------------------------------------------

QUERY PLAN

---------------------------------------------------------------------------------------------------------------

Seq Scan on public.base_tbl

Output: base_tbl.a

Filter: (SubPlan 1)

Filter: (ANY ((base_tbl.a = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2)))

SubPlan 1

-> Result

Output: base_tbl.a, (random() > '0'::double precision)

which shows that the planner thinks that sorting <literal>onek</literal> by

index-scanning is about 12% more expensive than sequential-scan-and-sort.

Of course, the next question is whether it's right about that.

We can investigate that using <command>EXPLAIN ANALYZE</command>, as discussed

below.

We can investigate that using <command>EXPLAIN ANALYZE</command>, as

discussed <link linkend="using-explain-analyze">below</link>.

</para>

<para>

<indexterm>

<primary>subplan</primary>

</indexterm>

Some query plans involve <firstterm>subplans</firstterm>, which arise

from sub-<literal>SELECT</literal>s in the original query. Such

queries can sometimes be transformed into ordinary join plans, but

when they cannot be, we get plans like:

<screen>

EXPLAIN VERBOSE SELECT unique1

FROM tenk1 t

WHERE t.ten &lt; ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);

QUERY PLAN

-------------------------------------------------------------------&zwsp;------

Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4)

Output: t.unique1

Filter: (ALL (t.ten &lt; (SubPlan 1).col1))

SubPlan 1

-&gt; Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4)

Output: o.ten

Filter: (o.four = t.four)

</screen>

This rather artificial example serves to illustrate a couple of

points: values from the outer plan level can be passed down into a

subplan (here, <literal>t.four</literal> is passed down) and the

results of the sub-select are available to the outer plan. Those

result values are shown by <command>EXPLAIN</command> with notations

like

<literal>(<replaceable>subplan_name</replaceable>).col<replaceable>N</replaceable></literal>,

which refers to the <replaceable>N</replaceable>'th output column of

the sub-<literal>SELECT</literal>.

</para>

<para>

<indexterm>

<primary>subplan</primary>

<secondary>hashed</secondary>

</indexterm>

In the example above, the <literal>ALL</literal> operator runs the

subplan again for each row of the outer query (which accounts for the

high estimated cost). Some queries can use a <firstterm>hashed

subplan</firstterm> to avoid that:

<screen>

EXPLAIN SELECT *

FROM tenk1 t

WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

QUERY PLAN

-------------------------------------------------------------------&zwsp;-------------------------

Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244)

Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))

SubPlan 1

-&gt; Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4)

(4 rows)

</screen>

Here, the subplan is run a single time and its output is loaded into

an in-memory hash table, which is then probed by the

outer <literal>ANY</literal> operator. This requires that the

sub-<literal>SELECT</literal> not reference any variables of the outer

query, and that the <literal>ANY</literal>'s comparison operator be

amenable to hashing.

</para>

<para>

<indexterm>

<primary>initplan</primary>

</indexterm>

If, in addition to not referencing any variables of the outer query,

the sub-<literal>SELECT</literal> cannot return more than one row,

it may instead be implemented as an <firstterm>initplan</firstterm>:

<screen>

EXPLAIN VERBOSE SELECT unique1

FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);

QUERY PLAN

------------------------------------------------------------&zwsp;--------

Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4)

Output: t1.unique1

Filter: (t1.ten = (InitPlan 1).col1)

InitPlan 1

-&gt; Result (cost=0.00..0.02 rows=1 width=4)

Output: ((random() * '10'::double precision))::integer

</screen>

An initplan is run only once per execution of the outer plan, and its

results are saved for re-use in later rows of the outer plan. So in

this example <literal>random()</literal> is evaluated only once and

all the values of <literal>t1.ten</literal> are compared to the same

randomly-chosen integer. That's quite different from what would

happen without the sub-<literal>SELECT</literal> construct.

</para>

</sect2>

staticvoidshow_instrumentation_count(constchar*qlabel,intwhich,

PlanState*planstate,ExplainState*es);

staticvoidshow_foreignscan_info(ForeignScanState*fsstate,ExplainState*es);

staticvoidshow_eval_params(Bitmapset*bms_params,ExplainState*es);

staticconstchar*explain_get_index_name(OidindexId);

staticboolpeek_buffer_usage(ExplainState*es,constBufferUsage*usage);

staticvoidshow_buffer_usage(ExplainState*es,constBufferUsage*usage);

ExplainPropertyInteger("Workers Planned",NULL,

gather->num_workers,es);

if (gather->initParam)

show_eval_params(gather->initParam,es);

if (es->analyze)

{

intnworkers;

ExplainPropertyInteger("Workers Planned",NULL,

gm->num_workers,es);

if (gm->initParam)

show_eval_params(gm->initParam,es);

if (es->analyze)

{

intnworkers;

}

}

show_eval_params(Bitmapset*bms_params,ExplainState*es)

{

intparamid=-1;

List*params=NIL;

Assert(bms_params);

while ((paramid=bms_next_member(bms_params,paramid)) >=0)

{

charparam[32];

snprintf(param,sizeof(param),"$%d",paramid);

params=lappend(params,pstrdup(param));

}

if (params)

ExplainPropertyList("Params Evaluated",params,es);

}

splan->plan_id);

splan->plan_name=palloc(32+12*list_length(splan->setParam));

sprintf(splan->plan_name,"%s %d",

isInitPlan ?"InitPlan" :"SubPlan",

splan->plan_id);

if (splan->setParam)

{

char*ptr=splan->plan_name+strlen(splan->plan_name);

ptr+=sprintf(ptr," (returns ");

foreach(lc,splan->setParam)

{

ptr+=sprintf(ptr,"$%d%s",

lfirst_int(lc),

lnext(splan->setParam,lc) ?"," :")");

}

}

splan->plan_name=psprintf("%s %d",

isInitPlan ?"InitPlan" :"SubPlan",

splan->plan_id);

cost_subplan(root,splan,plan);

node=makeNode(SubPlan);

node->subLinkType=EXPR_SUBLINK;

node->plan_id=list_length(root->glob->subplans);

node->plan_name=psprintf("InitPlan %d (returns $%d)",

node->plan_id,prm->paramid);

node->plan_name=psprintf("InitPlan %d",node->plan_id);

get_first_col_type(plan,&node->firstColType,&node->firstColTypmod,

&node->firstColCollation);

node->parallel_safe=plan->parallel_safe;