EXPLAIN - Shows statement execution plan


SYNOPSIS

       EXPLAIN [ VERBOSE ] query

   INPUTS
       VERBOSE
              Flag to show detailed query plan.

       query  Any query.

   OUTPUTS
       NOTICE: QUERY PLAN:
              Explicit query plan from the Postgres backend.

       EXPLAIN
              Flag sent after query plan is shown.


DESCRIPTION

       This command displays the execution plan that the Postgres
       planner generates for the supplied  query.  The  execution
       plan  shows  how the table(s) referenced by the query will
       be  scanned---by  plain  sequential  scan,   index   scan,
       etc.---and  if  multiple  tables are referenced, what join
       algorithms will be used to  bring  together  the  required
       tuples from each input table.

       The  most  critical  part  of the display is the estimated
       query execution cost, which is the planner's guess at  how
       long  it  will take to run the query (measured in units of
       disk page fetches). Actually two numbers  are  shown:  the
       start-up  time before the first tuple can be returned, and
       the total time to return all the tuples. For most  queries
       the total time is what matters, but in contexts such as an
       EXISTS sub-query the  planner  will  choose  the  smallest
       start-up  time  instead  of the smallest total time (since
       the executor will stop after getting one  tuple,  anyway).
       Also,  if  you limit the number of tuples to return with a
       LIMIT clause, the planner makes an appropriate  interpola­
       tion  between the endpoint costs to estimate which plan is
       really the cheapest.

       The VERBOSE option emits the full internal  representation
       of the plan tree, rather than just a summary (and sends it
       to the postmaster log file, too).  Usually this option  is
       only useful for debugging Postgres.

   NOTES
       There  is only sparse documentation on the optimizer's use

       cost  estimation  for  query  optimization can be found in
       database textbooks.  Refer to the  Programmer's  Guide  in
       the  chapters  on  indexes and the genetic query optimizer
       for more information.


USAGE

       To show a query plan for a simple query on a table with  a
       single int4 column and 128 rows:

       EXPLAIN SELECT * FROM foo;
           NOTICE:  QUERY PLAN:

       Seq Scan on foo  (cost=0.00..2.28 rows=128 width=4)

       EXPLAIN

       For  the  same  table with an index to support an equijoin
       condition on the query,  EXPLAIN  will  show  a  different
       plan:

       EXPLAIN SELECT * FROM foo WHERE i = 4;
           NOTICE:  QUERY PLAN:

       Index Scan using fi on foo  (cost=0.00..0.42 rows=1 width=4)

       EXPLAIN

       And  finally,  for the same table with an index to support
       an equijoin condition on the query, EXPLAIN will show  the
       following for a query using an aggregate function:

       EXPLAIN SELECT sum(i) FROM foo WHERE i = 4;
           NOTICE:  QUERY PLAN:

       Aggregate  (cost=0.42..0.42 rows=1 width=4)
         ->  Index Scan using fi on foo  (cost=0.00..0.42 rows=1 width=4)

       Note  that  the  specific  numbers  shown,  and  even  the
       selected  query  strategy,  may  vary   between   Postgres
       releases due to planner improvements.


COMPATIBILITY

   SQL92
       There is no EXPLAIN statement defined in SQL92.


Man(1) output converted with man2html