1EXPLAIN(7)                       SQL Commands                       EXPLAIN(7)
2
3
4

NAME

6       EXPLAIN - show the execution plan of a statement
7
8

SYNOPSIS

10       EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
11
12

DESCRIPTION

14       This  command  displays  the execution plan that the PostgreSQL planner
15       generates for the supplied statement. The execution plan shows how  the
16       table(s) referenced by the statement will be scanned — by plain sequen‐
17       tial scan, index scan, etc. — and if multiple  tables  are  referenced,
18       what  join  algorithms will be used to bring together the required rows
19       from each input table.
20
21       The most critical part of the display is the estimated statement execu‐
22       tion cost, which is the planner's guess at how long it will take to run
23       the statement (measured in units of disk page  fetches).  Actually  two
24       numbers  are  shown:  the  start-up  time  before  the first row can be
25       returned, and the total time to return all the rows. For  most  queries
26       the  total  time is what matters, but in contexts such as a subquery in
27       EXISTS, the planner will choose the smallest start-up time  instead  of
28       the smallest total time (since the executor will stop after getting one
29       row, anyway).  Also, if you limit the number of rows to return  with  a
30       LIMIT  clause,  the  planner makes an appropriate interpolation between
31       the endpoint costs to estimate which plan is really the cheapest.
32
33       The ANALYZE option causes the statement to be  actually  executed,  not
34       only planned. The total elapsed time expended within each plan node (in
35       milliseconds) and total number of rows it actually returned  are  added
36       to  the  display. This is useful for seeing whether the planner's esti‐
37       mates are close to reality.
38
39              Important: Keep in mind that the statement is actually  executed
40              when  the  ANALYZE option is used. Although EXPLAIN will discard
41              any output that a SELECT would return, other side effects of the
42              statement  will happen as usual. If you wish to use EXPLAIN ANA‐
43              LYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS,  or  EXECUTE
44              statement without letting the command affect your data, use this
45              approach:
46
47              BEGIN;
48              EXPLAIN ANALYZE ...;
49              ROLLBACK;
50
51
52

PARAMETERS

54       ANALYZE
55              Carry out the command and show the actual run times.
56
57       VERBOSE
58              Include the output column list for each node in the plan tree.
59
60       statement
61              Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or
62              CREATE TABLE AS statement, whose execution plan you wish to see.
63

NOTES

65       There  is  only  sparse  documentation  on  the optimizer's use of cost
66       information in PostgreSQL. Refer  to  in  the  documentation  for  more
67       information.
68
69       In  order  to  allow  the  PostgreSQL  query planner to make reasonably
70       informed decisions when optimizing queries,  the  ANALYZE  [analyze(7)]
71       statement  should be run to record statistics about the distribution of
72       data within the table. If you have not done this (or if the statistical
73       distribution  of  the data in the table has changed significantly since
74       the last time ANALYZE was run), the estimated  costs  are  unlikely  to
75       conform  to the real properties of the query, and consequently an infe‐
76       rior query plan might be chosen.
77
78       Genetic query  optimization  (GEQO)  randomly  tests  execution  plans.
79       Therefore,  when  the  number  of join relations exceeds geqo_threshold
80       causing genetic query optimization to be used, the  execution  plan  is
81       likely to change each time the statement is executed.
82
83       In  order  to  measure  the run-time cost of each node in the execution
84       plan, the current implementation of EXPLAIN ANALYZE can  add  consider‐
85       able  profiling  overhead  to  query  execution.  As  a result, running
86       EXPLAIN ANALYZE on a query can sometimes take significantly longer than
87       executing  the  query  normally.  The amount of overhead depends on the
88       nature of the query.
89

EXAMPLES

91       To show the plan for a simple query on a table with  a  single  integer
92       column and 10000 rows:
93
94       EXPLAIN SELECT * FROM foo;
95
96                              QUERY PLAN
97       ---------------------------------------------------------
98        Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
99       (1 row)
100
101
102       If  there is an index and we use a query with an indexable WHERE condi‐
103       tion, EXPLAIN might show a different plan:
104
105       EXPLAIN SELECT * FROM foo WHERE i = 4;
106
107                                QUERY PLAN
108       --------------------------------------------------------------
109        Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
110          Index Cond: (i = 4)
111       (2 rows)
112
113
114       Here is an example of a query plan for a query using an aggregate func‐
115       tion:
116
117       EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
118
119                                    QUERY PLAN
120       ---------------------------------------------------------------------
121        Aggregate  (cost=23.93..23.93 rows=1 width=4)
122          ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
123                Index Cond: (i < 10)
124       (3 rows)
125
126
127       Here  is  an  example of using EXPLAIN EXECUTE to display the execution
128       plan for a prepared query:
129
130       PREPARE query(int, int) AS SELECT sum(bar) FROM test
131           WHERE id > $1 AND id < $2
132           GROUP BY foo;
133
134       EXPLAIN ANALYZE EXECUTE query(100, 200);
135
136                                                              QUERY PLAN
137       -------------------------------------------------------------------------------------------------------------------------
138        HashAggregate  (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
139          ->  Index Scan using test_pkey on test  (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
140                Index Cond: ((id > $1) AND (id < $2))
141        Total runtime: 0.851 ms
142       (4 rows)
143
144
145       Of course, the specific numbers shown here depend on  the  actual  con‐
146       tents  of the tables involved. Also note that the numbers, and even the
147       selected query strategy, might vary between PostgreSQL releases due  to
148       planner improvements. In addition, the ANALYZE command uses random sam‐
149       pling to estimate data statistics; therefore, it is possible  for  cost
150       estimates  to  change  after a fresh run of ANALYZE, even if the actual
151       distribution of data in the table has not changed.
152

COMPATIBILITY

154       There is no EXPLAIN statement defined in the SQL standard.
155

SEE ALSO

157       ANALYZE [analyze(7)]
158
159
160
161SQL - Language Statements         2011-09-22                        EXPLAIN(7)
Impressum