Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 0628670

Browse filesBrowse files
tglsfdctglsfdc
committed
Invent SERIALIZE option for EXPLAIN.
EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about the volume of data emitted by a query, as well as the time taken to convert the data to the on-the-wire format. Previously there was no way to investigate this without actually sending the data to the client, in which case network transmission costs might swamp what you wanted to see. In particular this feature allows investigating the costs of de-TOASTing compressed or out-of-line data during formatting. Stepan Rutz and Matthias van de Meent, reviewed by Tomas Vondra and myself Discussion: https://postgr.es/m/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de
1 parent 97ce821 commit 0628670
Copy full SHA for 0628670

File tree

Expand file treeCollapse file tree

10 files changed

+542
-11
lines changed
Filter options
Expand file treeCollapse file tree

10 files changed

+542
-11
lines changed

‎doc/src/sgml/perform.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/perform.sgml
+18-6Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -144,11 +144,11 @@ EXPLAIN SELECT * FROM tenk1;
144144
It's important to understand that the cost of an upper-level node includes
145145
the cost of all its child nodes. It's also important to realize that
146146
the cost only reflects things that the planner cares about.
147-
In particular, the cost does not consider the time spent transmitting
148-
result rows to the client, which could be an important
149-
factor in the real elapsed time; but the planner ignores it because
150-
it cannot change it by altering the plan. (Every correct plan will
151-
output the same row set, we trust.)
147+
In particular, the cost does not consider the time spent to convert
148+
output values to text form or to transmit them to the client, which
149+
could be important factors in the real elapsed time; but the planner
150+
ignores those costs because it cannot change them by altering the
151+
plan. (Every correct plan will output the same row set, we trust.)
152152
</para>
153153

154154
<para>
@@ -956,6 +956,17 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
956956
<command>EXPLAIN ANALYZE</command>.
957957
</para>
958958

959+
<para>
960+
The time shown for the top-level node does not include any time needed
961+
to convert the query's output data into displayable form or to send it
962+
to the client. While <command>EXPLAIN ANALYZE</command> will never
963+
send the data to the client, it can be told to convert the query's
964+
output data to displayable form and measure the time needed for that,
965+
by specifying the <literal>SERIALIZE</literal> option. That time will
966+
be shown separately, and it's also included in the
967+
total <literal>Execution time</literal>.
968+
</para>
969+
959970
</sect2>
960971

961972
<sect2 id="using-explain-caveats">
@@ -965,7 +976,8 @@ EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
965976
There are two significant ways in which run times measured by
966977
<command>EXPLAIN ANALYZE</command> can deviate from normal execution of
967978
the same query. First, since no output rows are delivered to the client,
968-
network transmission costs and I/O conversion costs are not included.
979+
network transmission costs are not included. I/O conversion costs are
980+
not included either unless <literal>SERIALIZE</literal> is specified.
969981
Second, the measurement overhead added by <command>EXPLAIN
970982
ANALYZE</command> can be significant, especially on machines with slow
971983
<function>gettimeofday()</function> operating-system calls. You can use the

‎doc/src/sgml/ref/explain.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/explain.sgml
+29Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ EXPLAIN [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] <rep
4141
SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
4242
GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]
4343
BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
44+
SERIALIZE [ { NONE | TEXT | BINARY } ]
4445
WAL [ <replaceable class="parameter">boolean</replaceable> ]
4546
TIMING [ <replaceable class="parameter">boolean</replaceable> ]
4647
SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
@@ -206,6 +207,34 @@ ROLLBACK;
206207
</listitem>
207208
</varlistentry>
208209

210+
<varlistentry>
211+
<term><literal>SERIALIZE</literal></term>
212+
<listitem>
213+
<para>
214+
Include information on the cost
215+
of <firstterm>serializing</firstterm> the query's output data, that
216+
is converting it to text or binary format to send to the client.
217+
This can be a significant part of the time required for regular
218+
execution of the query, if the datatype output functions are
219+
expensive or if <acronym>TOAST</acronym>ed values must be fetched
220+
from out-of-line storage. <command>EXPLAIN</command>'s default
221+
behavior, <literal>SERIALIZE NONE</literal>, does not perform these
222+
conversions. If <literal>SERIALIZE TEXT</literal>
223+
or <literal>SERIALIZE BINARY</literal> is specified, the appropriate
224+
conversions are performed, and the time spent doing so is measured
225+
(unless <literal>TIMING OFF</literal> is specified). If
226+
the <literal>BUFFERS</literal> option is also specified, then any
227+
buffer accesses involved in the conversions are counted too.
228+
In no case, however, will <command>EXPLAIN</command> actually send
229+
the resulting data to the client; hence network transmission costs
230+
cannot be investigated this way.
231+
Serialization may only be enabled when <literal>ANALYZE</literal> is
232+
also enabled. If <literal>SERIALIZE</literal> is written without an
233+
argument, <literal>TEXT</literal> is assumed.
234+
</para>
235+
</listitem>
236+
</varlistentry>
237+
209238
<varlistentry>
210239
<term><literal>WAL</literal></term>
211240
<listitem>

‎src/backend/access/common/printtup.c

Copy file name to clipboardExpand all lines: src/backend/access/common/printtup.c
+4-1Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -294,6 +294,9 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
294294

295295
/* ----------------
296296
* printtup --- send a tuple to the client
297+
*
298+
* Note: if you change this function, see also serializeAnalyzeReceive
299+
* in explain.c, which is meant to replicate the computations done here.
297300
* ----------------
298301
*/
299302
static bool
@@ -317,7 +320,7 @@ printtup(TupleTableSlot *slot, DestReceiver *self)
317320
oldcontext = MemoryContextSwitchTo(myState->tmpcontext);
318321

319322
/*
320-
* Prepare a DataRow message (note buffer is in per-row context)
323+
* Prepare a DataRow message (note buffer is in per-query context)
321324
*/
322325
pq_beginmessage_reuse(buf, 'D');
323326

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.