Skip to content

Commit 76cbf7e

Browse files
michaelpqmichaelpq
committed
pg_stat_statements: Track I/O timing for temporary file blocks
This commit adds two new columns to pg_stat_statements, called temp_blk_read_time and temp_blk_write_time. Those columns respectively show the time spent to read and write temporary file blocks on disk, whose tracking has been added in efb0ef9. This information is available when track_io_timing is enabled, like blk_read_time and blk_write_time. pg_stat_statements is updated to version to 1.10 as an effect of the newly-added columns. Tests for the upgrade path 1.9->1.10 are added. PGSS_FILE_HEADER is bumped for the new stats file format. Author: Masahiko Sawada Reviewed-by: Georgios Kokolatos, Melanie Plageman, Julien Rouhaud, Ranier Vilela Discussion: https://postgr.es/m/CAD21AoAJgotTeP83p6HiAGDhs_9Fw9pZ2J=_tYTsiO5Ob-V5GQ@mail.gmail.com
1 parent a6baa4b commit 76cbf7e

File tree

7 files changed

+237
-9
lines changed

7 files changed

+237
-9
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,8 @@ OBJS = \
66
pg_stat_statements.o
77

88
EXTENSION = pg_stat_statements
9-
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
9+
DATA = pg_stat_statements--1.4.sql \
10+
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
1011
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
1112
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
1213
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \

‎contrib/pg_stat_statements/expected/oldextversions.out

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,4 +136,110 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
136136

137137
(1 row)
138138

139+
-- New function pg_stat_statement_info, and new function
140+
-- and view for pg_stat_statements introduced in 1.9
141+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
142+
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
143+
pg_get_functiondef
144+
-------------------------------------------------------------------------------------------------------------------------
145+
CREATE OR REPLACE FUNCTION public.pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone)+
146+
RETURNS record +
147+
LANGUAGE c +
148+
PARALLEL SAFE STRICT +
149+
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_info$function$ +
150+
151+
(1 row)
152+
153+
\d pg_stat_statements
154+
View "public.pg_stat_statements"
155+
Column | Type | Collation | Nullable | Default
156+
---------------------+------------------+-----------+----------+---------
157+
userid | oid | | |
158+
dbid | oid | | |
159+
toplevel | boolean | | |
160+
queryid | bigint | | |
161+
query | text | | |
162+
plans | bigint | | |
163+
total_plan_time | double precision | | |
164+
min_plan_time | double precision | | |
165+
max_plan_time | double precision | | |
166+
mean_plan_time | double precision | | |
167+
stddev_plan_time | double precision | | |
168+
calls | bigint | | |
169+
total_exec_time | double precision | | |
170+
min_exec_time | double precision | | |
171+
max_exec_time | double precision | | |
172+
mean_exec_time | double precision | | |
173+
stddev_exec_time | double precision | | |
174+
rows | bigint | | |
175+
shared_blks_hit | bigint | | |
176+
shared_blks_read | bigint | | |
177+
shared_blks_dirtied | bigint | | |
178+
shared_blks_written | bigint | | |
179+
local_blks_hit | bigint | | |
180+
local_blks_read | bigint | | |
181+
local_blks_dirtied | bigint | | |
182+
local_blks_written | bigint | | |
183+
temp_blks_read | bigint | | |
184+
temp_blks_written | bigint | | |
185+
blk_read_time | double precision | | |
186+
blk_write_time | double precision | | |
187+
wal_records | bigint | | |
188+
wal_fpi | bigint | | |
189+
wal_bytes | numeric | | |
190+
191+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
192+
has_data
193+
----------
194+
t
195+
(1 row)
196+
197+
-- New functions and views for pg_stat_statements in 1.10
198+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
199+
\d pg_stat_statements
200+
View "public.pg_stat_statements"
201+
Column | Type | Collation | Nullable | Default
202+
---------------------+------------------+-----------+----------+---------
203+
userid | oid | | |
204+
dbid | oid | | |
205+
toplevel | boolean | | |
206+
queryid | bigint | | |
207+
query | text | | |
208+
plans | bigint | | |
209+
total_plan_time | double precision | | |
210+
min_plan_time | double precision | | |
211+
max_plan_time | double precision | | |
212+
mean_plan_time | double precision | | |
213+
stddev_plan_time | double precision | | |
214+
calls | bigint | | |
215+
total_exec_time | double precision | | |
216+
min_exec_time | double precision | | |
217+
max_exec_time | double precision | | |
218+
mean_exec_time | double precision | | |
219+
stddev_exec_time | double precision | | |
220+
rows | bigint | | |
221+
shared_blks_hit | bigint | | |
222+
shared_blks_read | bigint | | |
223+
shared_blks_dirtied | bigint | | |
224+
shared_blks_written | bigint | | |
225+
local_blks_hit | bigint | | |
226+
local_blks_read | bigint | | |
227+
local_blks_dirtied | bigint | | |
228+
local_blks_written | bigint | | |
229+
temp_blks_read | bigint | | |
230+
temp_blks_written | bigint | | |
231+
blk_read_time | double precision | | |
232+
blk_write_time | double precision | | |
233+
temp_blk_read_time | double precision | | |
234+
temp_blk_write_time | double precision | | |
235+
wal_records | bigint | | |
236+
wal_fpi | bigint | | |
237+
wal_bytes | numeric | | |
238+
239+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
240+
has_data
241+
----------
242+
t
243+
(1 row)
244+
139245
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
5+
6+
/* First we have to remove them from the extension */
7+
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
8+
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
9+
10+
/* Then we can drop them */
11+
DROP VIEW pg_stat_statements;
12+
DROP FUNCTION pg_stat_statements(boolean);
13+
14+
/* Now redefine */
15+
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
16+
OUT userid oid,
17+
OUT dbid oid,
18+
OUT toplevel bool,
19+
OUT queryid bigint,
20+
OUT query text,
21+
OUT plans int8,
22+
OUT total_plan_time float8,
23+
OUT min_plan_time float8,
24+
OUT max_plan_time float8,
25+
OUT mean_plan_time float8,
26+
OUT stddev_plan_time float8,
27+
OUT calls int8,
28+
OUT total_exec_time float8,
29+
OUT min_exec_time float8,
30+
OUT max_exec_time float8,
31+
OUT mean_exec_time float8,
32+
OUT stddev_exec_time float8,
33+
OUT rows int8,
34+
OUT shared_blks_hit int8,
35+
OUT shared_blks_read int8,
36+
OUT shared_blks_dirtied int8,
37+
OUT shared_blks_written int8,
38+
OUT local_blks_hit int8,
39+
OUT local_blks_read int8,
40+
OUT local_blks_dirtied int8,
41+
OUT local_blks_written int8,
42+
OUT temp_blks_read int8,
43+
OUT temp_blks_written int8,
44+
OUT blk_read_time float8,
45+
OUT blk_write_time float8,
46+
OUT temp_blk_read_time float8,
47+
OUT temp_blk_write_time float8,
48+
OUT wal_records int8,
49+
OUT wal_fpi int8,
50+
OUT wal_bytes numeric
51+
)
52+
RETURNS SETOF record
53+
AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
54+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
55+
56+
CREATE VIEW pg_stat_statements AS
57+
SELECT * FROM pg_stat_statements(true);
58+
59+
GRANT SELECT ON pg_stat_statements TO PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 33 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ PG_MODULE_MAGIC;
8383
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
8484

8585
/* Magic number identifying the stats file format */
86-
static const uint32 PGSS_FILE_HEADER = 0x20201227;
86+
static const uint32 PGSS_FILE_HEADER = 0x20220408;
8787

8888
/* PostgreSQL major version number, changes in which invalidate all entries */
8989
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -116,7 +116,8 @@ typedef enum pgssVersion
116116
PGSS_V1_2,
117117
PGSS_V1_3,
118118
PGSS_V1_8,
119-
PGSS_V1_9
119+
PGSS_V1_9,
120+
PGSS_V1_10
120121
} pgssVersion;
121122

122123
typedef enum pgssStoreKind
@@ -178,8 +179,11 @@ typedef struct Counters
178179
int64 local_blks_written; /* # of local disk blocks written */
179180
int64 temp_blks_read; /* # of temp blocks read */
180181
int64 temp_blks_written; /* # of temp blocks written */
181-
double blk_read_time; /* time spent reading, in msec */
182-
double blk_write_time; /* time spent writing, in msec */
182+
double blk_read_time; /* time spent reading blocks, in msec */
183+
double blk_write_time; /* time spent writing blocks, in msec */
184+
double temp_blk_read_time; /* time spent reading temp blocks, in msec */
185+
double temp_blk_write_time; /* time spent writing temp blocks, in
186+
* msec */
183187
double usage; /* usage factor */
184188
int64 wal_records; /* # of WAL records generated */
185189
int64 wal_fpi; /* # of WAL full page images generated */
@@ -297,6 +301,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
297301
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
298302
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
299303
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
304+
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
300305
PG_FUNCTION_INFO_V1(pg_stat_statements);
301306
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
302307

@@ -1364,6 +1369,8 @@ pgss_store(const char *query, uint64 queryId,
13641369
e->counters.temp_blks_written += bufusage->temp_blks_written;
13651370
e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
13661371
e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
1372+
e->counters.temp_blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_read_time);
1373+
e->counters.temp_blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_write_time);
13671374
e->counters.usage += USAGE_EXEC(total_time);
13681375
e->counters.wal_records += walusage->wal_records;
13691376
e->counters.wal_fpi += walusage->wal_fpi;
@@ -1417,7 +1424,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
14171424
#define PG_STAT_STATEMENTS_COLS_V1_3 23
14181425
#define PG_STAT_STATEMENTS_COLS_V1_8 32
14191426
#define PG_STAT_STATEMENTS_COLS_V1_9 33
1420-
#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
1427+
#define PG_STAT_STATEMENTS_COLS_V1_10 35
1428+
#define PG_STAT_STATEMENTS_COLS 35 /* maximum of above */
14211429

14221430
/*
14231431
* Retrieve statement statistics.
@@ -1429,6 +1437,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
14291437
* expected API version is identified by embedding it in the C name of the
14301438
* function. Unfortunately we weren't bright enough to do that for 1.1.
14311439
*/
1440+
Datum
1441+
pg_stat_statements_1_10(PG_FUNCTION_ARGS)
1442+
{
1443+
bool showtext = PG_GETARG_BOOL(0);
1444+
1445+
pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
1446+
1447+
return (Datum) 0;
1448+
}
1449+
14321450
Datum
14331451
pg_stat_statements_1_9(PG_FUNCTION_ARGS)
14341452
{
@@ -1542,6 +1560,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
15421560
if (api_version != PGSS_V1_9)
15431561
elog(ERROR, "incorrect number of output arguments");
15441562
break;
1563+
case PG_STAT_STATEMENTS_COLS_V1_10:
1564+
if (api_version != PGSS_V1_10)
1565+
elog(ERROR, "incorrect number of output arguments");
1566+
break;
15451567
default:
15461568
elog(ERROR, "incorrect number of output arguments");
15471569
}
@@ -1742,6 +1764,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
17421764
values[i++] = Float8GetDatumFast(tmp.blk_read_time);
17431765
values[i++] = Float8GetDatumFast(tmp.blk_write_time);
17441766
}
1767+
if (api_version >= PGSS_V1_10)
1768+
{
1769+
values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
1770+
values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
1771+
}
17451772
if (api_version >= PGSS_V1_8)
17461773
{
17471774
char buf[256];
@@ -1766,6 +1793,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
17661793
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
17671794
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
17681795
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
1796+
api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
17691797
-1 /* fail if you forget to update this assert */ ));
17701798

17711799
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);

‎contrib/pg_stat_statements/pg_stat_statements.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track planning and execution statistics of all SQL statements executed'
3-
default_version = '1.9'
3+
default_version = '1.10'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎contrib/pg_stat_statements/sql/oldextversions.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,4 +36,16 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
3636
\d pg_stat_statements
3737
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
3838

39+
-- New function pg_stat_statement_info, and new function
40+
-- and view for pg_stat_statements introduced in 1.9
41+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
42+
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
43+
\d pg_stat_statements
44+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
45+
46+
-- New functions and views for pg_stat_statements in 1.10
47+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
48+
\d pg_stat_statements
49+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
50+
3951
DROP EXTENSION pg_stat_statements;

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -338,7 +338,7 @@
338338
<structfield>blk_read_time</structfield> <type>double precision</type>
339339
</para>
340340
<para>
341-
Total time the statement spent reading blocks, in milliseconds
341+
Total time the statement spent reading data file blocks, in milliseconds
342342
(if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
343343
</para></entry>
344344
</row>
@@ -348,11 +348,33 @@
348348
<structfield>blk_write_time</structfield> <type>double precision</type>
349349
</para>
350350
<para>
351-
Total time the statement spent writing blocks, in milliseconds
351+
Total time the statement spent writing data file blocks, in milliseconds
352352
(if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
353353
</para></entry>
354354
</row>
355355

356+
<row>
357+
<entry role="catalog_table_entry"><para role="column_definition">
358+
<structfield>temp_blk_read_time</structfield> <type>double precision</type>
359+
</para>
360+
<para>
361+
Total time the statement spent reading temporary file blocks, in
362+
milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
363+
otherwise zero)
364+
</para></entry>
365+
</row>
366+
367+
<row>
368+
<entry role="catalog_table_entry"><para role="column_definition">
369+
<structfield>temp_blk_write_time</structfield> <type>double precision</type>
370+
</para>
371+
<para>
372+
Total time the statement spent writing temporary file blocks, in
373+
milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
374+
otherwise zero)
375+
</para></entry>
376+
</row>
377+
356378
<row>
357379
<entry role="catalog_table_entry"><para role="column_definition">
358380
<structfield>wal_records</structfield> <type>bigint</type>

0 commit comments

Comments
 (0)