1EXPLAIN() SQL Commands EXPLAIN()
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 ANALYZE is used. Although EXPLAIN will discard any output
41 that a SELECT would return, other side effects of the statement
42 will happen as usual. If you wish to use EXPLAIN ANALYZE on an
43 INSERT, UPDATE, DELETE, or EXECUTE statement without letting the
44 command affect your data, use this approach:
45 BEGIN;
47 EXPLAIN ANALYZE ...;
48 ROLLBACK;
49
53 ANALYZE
54 Carry out the command and show the actual run times.
55 VERBOSE
57 Show the full internal representation of the plan tree, rather
58 than just a summary. Usually this option is only useful for spe‐
59 cialized debugging purposes. The VERBOSE output is either
60 pretty-printed or not, depending on the setting of the
61 explain_pretty_print configuration parameter.
62 statement
64 Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, or DECLARE
65 statement, whose execution plan you wish to see.
66
68 There is only sparse documentation on the optimizer's use of cost
69 information in PostgreSQL. Refer to in the documentation for more
70 information.
71 In order to allow the PostgreSQL query planner to make reasonably
73 informed decisions when optimizing queries, the ANALYZE statement
74 should be run to record statistics about the distribution of data
75 within the table. If you have not done this (or if the statistical dis‐
76 tribution of the data in the table has changed significantly since the
77 last time ANALYZE was run), the estimated costs are unlikely to conform
78 to the real properties of the query, and consequently an inferior query
79 plan may be chosen.
80 Genetic query optimization (GEQO) randomly tests execution plans.
82 Therefore, when the number of tables exceeds geqo_threshold causing
83 genetic query optimization to be used, the execution plan is likely to
84 change each time the statement is executed.
85
87 To show the plan for a simple query on a table with a single integer
88 column and 10000 rows:
89 EXPLAIN SELECT * FROM foo;
91 QUERY PLAN
93 ---------------------------------------------------------
94 Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
95 (1 row)
96 If there is an index and we use a query with an indexable WHERE condi‐
99 tion, EXPLAIN might show a different plan:
100 EXPLAIN SELECT * FROM foo WHERE i = 4;
102 QUERY PLAN
104 --------------------------------------------------------------
105 Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
106 Index Cond: (i = 4)
107 (2 rows)
108 And here is an example of a query plan for a query using an aggregate
111 function:
112 EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
114 QUERY PLAN
116 ---------------------------------------------------------------------
117 Aggregate (cost=23.93..23.93 rows=1 width=4)
118 -> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)
119 Index Cond: (i < 10)
120 (3 rows)
121 Here is an example of using EXPLAIN EXECUTE to display the execution
124 plan for a prepared query:
125 PREPARE query(int, int) AS SELECT sum(bar) FROM test
127 WHERE id > $1 AND id < $2
128 GROUP BY foo;
129 EXPLAIN ANALYZE EXECUTE query(100, 200);
131 QUERY PLAN
133 -------------------------------------------------------------------------------------------------------------------------
134 HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
135 -> 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)
136 Index Cond: ((id > $1) AND (id < $2))
137 Total runtime: 0.851 ms
138 (4 rows)
139 Of course, the specific numbers shown here depend on the actual con‐
142 tents of the tables involved. Also note that the numbers, and even the
143 selected query strategy, may vary between PostgreSQL releases due to
144 planner improvements. In addition, the ANALYZE command uses random sam‐
145 pling to estimate data statistics; therefore, it is possible for cost
146 estimates to change after a fresh run of ANALYZE, even if the actual
147 distribution of data in the table has not changed.
148
150 There is no EXPLAIN statement defined in the SQL standard.
151
153 ANALYZE [analyze(7)]
154SQL - Language Statements 2008-06-08 EXPLAIN()