1EXPLAIN(7) SQL Commands EXPLAIN(7)
2
6 EXPLAIN - show the execution plan of a statement
7
10 EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
11
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 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 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 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 BEGIN;
48 EXPLAIN ANALYZE ...;
49 ROLLBACK;
50
54 ANALYZE
55 Carry out the command and show the actual run times.
56 VERBOSE
58 Include the output column list for each node in the plan tree.
59 statement
61 Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or
62 CREATE TABLE AS statement, whose execution plan you wish to see.
63
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 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 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 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
91 To show the plan for a simple query on a table with a single integer
92 column and 10000 rows:
93 EXPLAIN SELECT * FROM foo;
95 QUERY PLAN
97 ---------------------------------------------------------
98 Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
99 (1 row)
100 If there is an index and we use a query with an indexable WHERE condi‐
103 tion, EXPLAIN might show a different plan:
104 EXPLAIN SELECT * FROM foo WHERE i = 4;
106 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 Here is an example of a query plan for a query using an aggregate func‐
115 tion:
116 EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
118 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 Here is an example of using EXPLAIN EXECUTE to display the execution
128 plan for a prepared query:
129 PREPARE query(int, int) AS SELECT sum(bar) FROM test
131 WHERE id > $1 AND id < $2
132 GROUP BY foo;
133 EXPLAIN ANALYZE EXECUTE query(100, 200);
135 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 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
154 There is no EXPLAIN statement defined in the SQL standard.
155
157 ANALYZE [analyze(7)]
158SQL - Language Statements 2011-09-22 EXPLAIN(7)