Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit0628670

Browse files
committed
Invent SERIALIZE option for EXPLAIN.
EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics aboutthe volume of data emitted by a query, as well as the time takento convert the data to the on-the-wire format. Previously therewas no way to investigate this without actually sending the datato the client, in which case network transmission costs mightswamp what you wanted to see. In particular this feature allowsinvestigating the costs of de-TOASTing compressed or out-of-linedata during formatting.Stepan Rutz and Matthias van de Meent,reviewed by Tomas Vondra and myselfDiscussion:https://postgr.es/m/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de
1 parent97ce821 commit0628670

File tree

10 files changed

+542
-11
lines changed

10 files changed

+542
-11
lines changed

‎doc/src/sgml/perform.sgml

Lines 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 spenttransmitting
148-
result rows tothe client, which could be an important
149-
factorin the real elapsed time; but the planner ignores it because
150-
it cannot changeit 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 spentto convert
148+
output values totext form or to transmit them to the client, which
149+
could be important factorsin the real elapsed time; but the planner
150+
ignores those costs becauseit cannot changethem by altering the
151+
plan. (Every correct plan willoutput 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

Lines 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

Lines 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
staticbool
@@ -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)
[8]ページ先頭
©2009-2025 Movatter.jp