longer than executing
the query normally. The amount of overhead depends on the nature of
the query, as well as the platform being used. The worst case occurs
for plan nodes that in themselves require very little time per
execution, and on machines that have relatively slow operating
system calls for obtaining the time of day.
</para>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
To show the plan for a simple query on a table with a single
<type>integer</type> column and 10000 rows:
<programlisting>
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(1 row)
</programlisting>
</para>
<para>
Here is the same query, with JSON output formatting:
<programlisting>
EXPLAIN (FORMAT JSON) SELECT * FROM foo;
QUERY PLAN
--------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan",+
"Relation Name": "foo", +
"Alias": "foo", +
"Startup Cost": 0.00, +
"Total Cost": 155.00, +
"Plan Rows": 10000, +
"Plan Width": 4 +
} +
} +
]
(1 row)
</programlisting>
</para>
<para>
If there is an index and we use a query with an indexable
<literal>WHERE</literal> condition, <command>EXPLAIN</command>
might show a different plan:
<programlisting>
EXPLAIN SELECT * FROM foo WHERE i = 4;
QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)
(2 rows)
</programlisting>
</para>
<para>
Here is the same query, but in YAML format:
<programlisting>
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
QUERY PLAN
-------------------------------
- Plan: +
Node Type: "Index Scan" +
Scan Direction: "Forward"+
Index Name: "fi" +
Relation Name: "foo" +
Alias: "foo" +
Startup Cost: 0.00 +
Total Cost: 5.98 +
Plan Rows: 1 +
Plan Width: 4 +
Index Cond: "(i = 4)"
(1 row)
</programlisting>
XML format is left as an exercise for the reader.
</para>
<para>
Here is the same plan with cost estimates suppressed:
<programlisting>
EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
QUERY PLAN
----------------------------
Index Scan using fi on foo
Index Cond: (i = 4)
(2 rows)
</programlisting>
</para>
<para>
Here is an example of a query plan for a query using an aggregate
function:
<programlisting>
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
QUERY PLAN
-------------------------------------------------------------------&zwsp;--
Aggregate (cost=23.93..23.93 rows=1 width=4)
-> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
Index Cond: (i < 10)
(3 rows)
</programlisting>
</para>
<para>
Here is an example of using <command>EXPLAIN EXECUTE</command> to
display the execution plan for a prepared query:
<programlisting>
PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE