| 1 |
<!-- doc/src/sgml/pgoverexplain.sgml --> |
| 2 |
|
| 3 |
<sect1 id="pgoverexplain" xreflabel="pg_overexplain"> |
| 4 |
<title>pg_overexplain — 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> |