Debian Codegraph @

doc/src/sgml/pgoverexplain.sgml

raw
1 <!-- doc/src/sgml/pgoverexplain.sgml -->
2
3 <sect1 id="pgoverexplain" xreflabel="pg_overexplain">
4 <title>pg_overexplain &mdash; allow EXPLAIN to dump even more details</title>
5
6 <indexterm zone="pgoverexplain">
7 <primary>pg_overexplain</primary>
8 </indexterm>
9
10 <para>
11 The <filename>pg_overexplain</filename> module extends <command>EXPLAIN</command>
12 with new options that provide additional output. It is mostly intended to
13 assist with debugging of and development of the planner, rather than for
14 general use. Since this module displays internal details of planner data
15 structures, it may be necessary to refer to the source code to make sense
16 of the output. Furthermore, the output is likely to change whenever (and as
17 often as) those data structures change.
18 </para>
19
20 <para>
21 To use it, simply load it into the server. You can load it into an
22 individual session:
23
24 <programlisting>
25 LOAD 'pg_overexplain';
26 </programlisting>
27
28 You can also preload it into some or all sessions by including
29 <literal>pg_overexplain</literal> in
30 <xref linkend="guc-session-preload-libraries"/> or
31 <xref linkend="guc-shared-preload-libraries"/> in
32 <filename>postgresql.conf</filename>.
33 </para>
34
35 <sect2 id="pgoverexplain-debug">
36 <title>EXPLAIN (DEBUG)</title>
37
38 <para>
39 The <literal>DEBUG</literal> option displays miscellaneous information from
40 the plan tree that is not normally shown because it is not expected to be
41 of general interest. For each individual plan node, it will display the
42 following fields. See <structname>Plan</structname> in
43 <filename>nodes/plannodes.h</filename> for additional documentation of these
44 fields.
45 </para>
46
47 <itemizedlist>
48 <listitem>
49 <para>
50 <literal>Disabled Nodes</literal>. Normal <command>EXPLAIN</command>
51 determines whether a node is disabled by checking whether the node's
52 count of disabled nodes is larger than the sum of the counts for the
53 underlying nodes. This option shows the raw counter value.
54 </para>
55 </listitem>
56
57 <listitem>
58 <para>
59 <literal>Parallel Safe</literal>. Indicates whether it would be safe for
60 a plan tree node to appear beneath a <literal>Gather</literal> or
61 <literal>Gather Merge</literal> node, regardless of whether it is
62 actually below such a node.
63 </para>
64 </listitem>
65
66 <listitem>
67 <para>
68 <literal>Plan Node ID</literal>. An internal ID number that should be
69 unique for every node in the plan tree. It is used to coordinate parallel
70 query activity.
71 </para>
72 </listitem>
73
74 <listitem>
75 <para>
76 <literal>extParam</literal> and <literal>allParam</literal>. Information
77 about which numbered parameters affect this plan node or its children. In
78 text mode, these fields are only displayed if they are non-empty sets.
79 </para>
80 </listitem>
81 </itemizedlist>
82
83 <para>
84 Once per query, the <literal>DEBUG</literal> option will display the
85 following fields. See <structname>PlannedStmt</structname> in
86 <filename>nodes/plannodes.h</filename> for additional detail.
87 </para>
88
89 <itemizedlist>
90 <listitem>
91 <para>
92 <literal>Command Type</literal>. For example, <literal>select</literal>
93 or <literal>update</literal>.
94 </para>
95 </listitem>
96
97 <listitem>
98 <para>
99 <literal>Flags</literal>. A comma-separated list of Boolean structure
100 member names from the <structname>PlannedStmt</structname> that are set to
101 <literal>true</literal>. It covers the following structure members:
102 <literal>hasReturning</literal>, <literal>hasModifyingCTE</literal>,
103 <literal>canSetTag</literal>, <literal>transientPlan</literal>,
104 <literal>dependsOnRole</literal>, <literal>parallelModeNeeded</literal>.
105 </para>
106 </listitem>
107
108 <listitem>
109 <para>
110 <literal>Subplans Needing Rewind</literal>. Integer IDs of subplans that
111 may need to be rewound by the executor.
112 </para>
113 </listitem>
114
115 <listitem>
116 <para>
117 <literal>Relation OIDs</literal>. OIDs of relations upon which this plan
118 depends.
119 </para>
120 </listitem>
121
122 <listitem>
123 <para>
124 <literal>Executor Parameter Types</literal>. Type OID for each executor parameter
125 (e.g. when a nested loop is chosen and a parameter is used to pass a value down
126 to an inner index scan). Does not include parameters supplied to a prepared
127 statement by the user.
128 </para>
129 </listitem>
130
131 <listitem>
132 <para>
133 <literal>Parse Location</literal>. Location within the query string
134 supplied to the planner where this query's text can be found. May be
135 <literal>Unknown</literal> in some contexts. Otherwise, may be
136 <literal>NNN to end</literal> for some integer <literal>NNN</literal> or
137 <literal>NNN for MMM bytes</literal> for some integers
138 <literal>NNN</literal> and <literal>MMM</literal>.
139 </para>
140 </listitem>
141 </itemizedlist>
142 </sect2>
143
144 <sect2 id="pgoverexplain-range-table">
145 <title>EXPLAIN (RANGE_TABLE)</title>
146
147 <para>
148 The <literal>RANGE_TABLE</literal> option displays information from the
149 plan tree specifically concerning the query's range table. Range table
150 entries correspond roughly to items appearing in the query's
151 <literal>FROM</literal> clause, but with numerous exceptions. For example,
152 subqueries that are proved unnecessary may be deleted from the range table
153 entirely, while inheritance expansion adds range table entries for child
154 tables that are not named directly in the query.
155 </para>
156
157 <para>
158 Range table entries are generally referenced within the query plan by a
159 range table index, or RTI. Plan nodes that reference one or more RTIs will
160 be labelled accordingly, using one of the following fields: <literal>Scan
161 RTI</literal>, <literal>Nominal RTI</literal>, <literal>Exclude Relation
162 RTI</literal>, <literal>Append RTIs</literal>.
163 </para>
164
165 <para>
166 In addition, the query as a whole may maintain lists of range table indexes
167 that are needed for various purposes. These lists will be displayed once
168 per query, labelled as appropriate as <literal>Unprunable RTIs</literal> or
169 <literal>Result RTIs</literal>. In text mode, these fields are only
170 displayed if they are non-empty sets.
171 </para>
172
173 <para>
174 Finally, but most importantly, the <literal>RANGE_TABLE</literal> option
175 will display a dump of the query's entire range table. Each range table
176 entry is labelled with the appropriate range table index, the kind of range
177 table entry (e.g. <literal>relation</literal>,
178 <literal>subquery</literal>, or <literal>join</literal>), followed by the
179 contents of various range table entry fields that are not normally part of
180 <command>EXPLAIN</command> output. Some of these fields are only displayed
181 for certain kinds of range table entries. For example,
182 <literal>Eref</literal> is displayed for all types of range table entries,
183 but <literal>CTE Name</literal> is displayed only for range table entries
184 of type <literal>cte</literal>.
185 </para>
186
187 <para>
188 For more information about range table entries, see the definition of
189 <structname>RangeTblEntry</structname> in <filename>nodes/parsenodes.h</filename>.
190 </para>
191 </sect2>
192
193 <sect2 id="pgoverexplain-author">
194 <title>Author</title>
195
196 <para>
197 Robert Haas <email>[email protected]</email>
198 </para>
199 </sect2>
200
201 </sect1>