Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/ref/explain.sgml`
beaa1d65250590cc03cebb0f4afe356ab80e659ec85b086f0000000100000d3b
 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 &lt; 10;

                             QUERY PLAN
-------------------------------------------------------------------&zwsp;--
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   -&gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i &lt; 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

Title: EXPLAIN Command Examples and Demonstration
Summary
This section presents several examples of using the EXPLAIN command with different queries and output formats. It illustrates how the execution plan changes based on the presence of indexes and WHERE conditions, and showcases the use of JSON and YAML output formats. It also shows how to suppress cost estimates and use EXPLAIN with aggregate functions. Finally, it demonstrates the usage of EXPLAIN EXECUTE with prepared queries.