VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]

COSTS [ <replaceable class="parameter">boolean</replaceable> ]

SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]

GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]

BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]

WAL [ <replaceable class="parameter">boolean</replaceable> ]

TIMING [ <replaceable class="parameter">boolean</replaceable> ]

</listitem>

</varlistentry>

<varlistentry>

<term><literal>GENERIC_PLAN</literal></term>

<listitem>

<para>

Allow the statement to contain parameter placeholders like

<literal>$1</literal>, and generate a generic plan that does not

depend on the values of those parameters.

See <link linkend="sql-prepare"><command>PREPARE</command></link>

for details about generic plans and the types of statement that

support parameters.

This parameter cannot be used together with <literal>ANALYZE</literal>.

It defaults to <literal>FALSE</literal>.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>BUFFERS</literal></term>

<listitem>

query processing.

The number of blocks shown for an

upper-level node includes those used by all its child nodes. In text

format, only non-zero values are printed.It defaults to

format, only non-zero values are printed.This parameter defaults to

<literal>FALSE</literal>.

</para>

</listitem>

EXPLAIN ANALYZE EXECUTE query(100, 200);

QUERY PLAN

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

HashAggregate (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)

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

HashAggregate (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1)

Group Key: foo

-&gt; Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)

Index Cond: ((id &gt; $1) AND (id &lt; $2))

Planning time: 0.197 ms

Execution time: 0.225 ms

(6 rows)

Batches: 1 Memory Usage: 24kB

-&gt; Index Scan using test_pkey on test (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1)

Index Cond: ((id &gt; 100) AND (id &lt; 200))

Planning Time: 0.244 ms

Execution Time: 0.073 ms

(7 rows)

</programlisting>

</para>

<command>ANALYZE</command>, even if the actual distribution of data

in the table has not changed.

</para>

<para>

Notice that the previous example showed a <quote>custom</quote> plan

for the specific parameter values given in <command>EXECUTE</command>.

We might also wish to see the generic plan for a parameterized

query, which can be done with <literal>GENERIC_PLAN</literal>:

<programlisting>

EXPLAIN (GENERIC_PLAN)

SELECT sum(bar) FROM test

WHERE id &gt; $1 AND id &lt; $2

GROUP BY foo;

QUERY PLAN

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

HashAggregate (cost=26.79..26.89 rows=10 width=12)

Group Key: foo

-&gt; Index Scan using test_pkey on test (cost=0.29..24.29 rows=500 width=8)

Index Cond: ((id &gt; $1) AND (id &lt; $2))

(4 rows)

</programlisting>

In this case the parser correctly inferred that <literal>$1</literal>

and <literal>$2</literal> should have the same data type

as <literal>id</literal>, so the lack of parameter type information

from <command>PREPARE</command> was not a problem. In other cases

it might be necessary to explicitly specify types for the parameter

symbols, which can be done by casting them, for example:

<programlisting>

EXPLAIN (GENERIC_PLAN)

SELECT sum(bar) FROM test

WHERE id &gt; $1::integer AND id &lt; $2::integer

GROUP BY foo;

</programlisting>

</para>

</refsect1>

<refsect1>

es->wal=defGetBoolean(opt);

elseif (strcmp(opt->defname,"settings")==0)

es->settings=defGetBoolean(opt);

elseif (strcmp(opt->defname,"generic_plan")==0)

es->generic=defGetBoolean(opt);

elseif (strcmp(opt->defname,"timing")==0)

{

timing_set= true;

parser_errposition(pstate,opt->location)));

}

if (es->wal&& !es->analyze)

ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("EXPLAIN option TIMING requires ANALYZE")));

if (es->generic&&es->analyze)

ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together")));

es->summary= (summary_set) ?es->summary :es->analyze;

eflags=0;/* default run-to-completion flags */

eflags=EXEC_FLAG_EXPLAIN_ONLY;

if (es->generic)

eflags |=EXEC_FLAG_EXPLAIN_GENERIC;

if (into)

eflags |=GetIntoRelEFlags(into);

&(EXEC_FLAG_EXPLAIN_ONLY |EXEC_FLAG_WITH_NO_DATA);

&~(EXEC_FLAG_REWIND |EXEC_FLAG_BACKWARD |EXEC_FLAG_MARK);

if (bms_is_member(i,plannedstmt->rewindPlanIDs))

sp_eflags |=EXEC_FLAG_REWIND;

pprune->present_parts=bms_copy(pinfo->present_parts);

pprune->initial_pruning_steps=pinfo->initial_pruning_steps;

if (pinfo->initial_pruning_steps)

if (pinfo->initial_pruning_steps&&

!(econtext->ecxt_estate->es_top_eflags&EXEC_FLAG_EXPLAIN_GENERIC))

{

InitPartitionPruneContext(&pprune->initial_context,

pinfo->initial_pruning_steps,

prunestate->do_initial_prune= true;

}

pprune->exec_pruning_steps=pinfo->exec_pruning_steps;

if (pinfo->exec_pruning_steps)

if (pinfo->exec_pruning_steps&&

!(econtext->ecxt_estate->es_top_eflags&EXEC_FLAG_EXPLAIN_GENERIC))

{

InitPartitionPruneContext(&pprune->exec_context,

pinfo->exec_pruning_steps,

transformExplainStmt(ParseState*pstate,ExplainStmt*stmt)

{

Query*result;

boolgeneric_plan= false;

Oid*paramTypes=NULL;

intnumParams=0;

if (pstate->p_paramref_hook==NULL)

{

ListCell*lc;

foreach(lc,stmt->options)

{

DefElem*opt= (DefElem*)lfirst(lc);

if (strcmp(opt->defname,"generic_plan")==0)

generic_plan=defGetBoolean(opt);

}

if (generic_plan)

setup_parse_variable_parameters(pstate,&paramTypes,&numParams);

}

stmt->query= (Node*)transformOptionalSelectInto(pstate,stmt->query);

if (generic_plan)

check_variable_parameters(pstate, (Query*)stmt->query);

result=makeNode(Query);

result->commandType=CMD_UTILITY;

if (ends_with(prev_wd,'(')||ends_with(prev_wd,','))

COMPLETE_WITH("ANALYZE","VERBOSE","COSTS","SETTINGS",

COMPLETE_WITH("ANALYZE","VERBOSE","COSTS","SETTINGS","GENERIC_PLAN",

"BUFFERS","WAL","TIMING","SUMMARY","FORMAT");

elseif (TailMatches("ANALYZE|VERBOSE|COSTS|SETTINGS|BUFFERS|WAL|TIMING|SUMMARY"))

elseif (TailMatches("ANALYZE|VERBOSE|COSTS|SETTINGS|GENERIC_PLAN|BUFFERS|WAL|TIMING|SUMMARY"))

COMPLETE_WITH("ON","OFF");

elseif (TailMatches("FORMAT"))

COMPLETE_WITH("TEXT","XML","JSON","YAML");

booltiming;/* print detailed node timing */

boolsummary;/* print total planning and execution timing */

boolsettings;/* print modified settings */

boolgeneric;/* generate a generic plan */

ExplainFormatformat;/* output format */

intindent;/* current indentation level */

#defineEXEC_FLAG_EXPLAIN_ONLY0x0001/* EXPLAIN, no ANALYZE */

#defineEXEC_FLAG_REWIND0x0002/* need efficient rescan */

#defineEXEC_FLAG_BACKWARD0x0004/* need backward scan */

#defineEXEC_FLAG_MARK0x0008/* need mark/restore */

#defineEXEC_FLAG_SKIP_TRIGGERS 0x0010/* skip AfterTrigger calls */

#defineEXEC_FLAG_WITH_NO_DATA0x0020/* rel scannability doesn't matter */

#defineEXEC_FLAG_EXPLAIN_ONLY0x0001/* EXPLAIN, no ANALYZE */

#defineEXEC_FLAG_EXPLAIN_GENERIC0x0002/* EXPLAIN (GENERIC_PLAN) */

#defineEXEC_FLAG_REWIND0x0004/* need efficient rescan */

#defineEXEC_FLAG_BACKWARD0x0008/* need backward scan */

#defineEXEC_FLAG_MARK0x0010/* need mark/restore */

#defineEXEC_FLAG_SKIP_TRIGGERS0x0020/* skip AfterTrigger setup */

#defineEXEC_FLAG_WITH_NO_DATA0x0040/* REFRESH ... WITH NO DATA */

(1 row)

rollback;

-- GENERIC_PLAN option

select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');

explain_filter

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

Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N)

Recheck Cond: (thousand = $N)

-> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N)

Index Cond: (thousand = $N)

(4 rows)

-- should fail

select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');

ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together

CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement

-- Test EXPLAIN (GENERIC_PLAN) with partition pruning

-- partitions should be pruned at plan time, based on constants,

-- but there should be no pruning based on parameter placeholders

create table gen_part (

key1 integer not null,

key2 integer not null

) partition by list (key1);

create table gen_part_1

partition of gen_part for values in (1)

partition by range (key2);

create table gen_part_1_1

partition of gen_part_1 for values from (1) to (2);

create table gen_part_1_2

partition of gen_part_1 for values from (2) to (3);

create table gen_part_2

partition of gen_part for values in (2);

-- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2

select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1');

explain_filter

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

Append (cost=N.N..N.N rows=N width=N)

-> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N)

Filter: ((key1 = N) AND (key2 = $N))

-> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N)

Filter: ((key1 = N) AND (key2 = $N))

(5 rows)

drop table gen_part;

--

-- Test production of per-worker data

--

select explain_filter_to_json('explain (settings, format json) select * from int8_tbl i8')#> '{0,Settings,plan_cache_mode}';

rollback;

select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');

select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');

createtablegen_part (

key1integernot null,

key2integernot null

) partition by list (key1);

partition of gen_part forvaluesin (1)

partition by range (key2);

partition of gen_part_1 forvaluesfrom (1) to (2);

partition of gen_part_1 forvaluesfrom (2) to (3);

partition of gen_part forvaluesin (2);

select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1');

droptable gen_part;