Skip to content

Commit 2254367

Browse files
robertmhaasrobertmhaas
committed
Make EXPLAIN (BUFFERS) track blocks dirtied, as well as those written.
Also expose the new counters through pg_stat_statements. Patch by me. Review by Fujii Masao and Greg Smith.
1 parent f74f9a2 commit 2254367

File tree

12 files changed

+148
-10
lines changed

12 files changed

+148
-10
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,8 @@ MODULE_big = pg_stat_statements
44
OBJS = pg_stat_statements.o
55

66
EXTENSION = pg_stat_statements
7-
DATA = pg_stat_statements--1.0.sql pg_stat_statements--unpackaged--1.0.sql
7+
DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
8+
pg_stat_statements--unpackaged--1.0.sql
89

910
ifdef USE_PGXS
1011
PG_CONFIG = pg_config

‎contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.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 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();
9+
10+
/* Then we can drop them */
11+
DROP VIEW pg_stat_statements;
12+
DROP FUNCTION pg_stat_statements();
13+
14+
/* Now redefine */
15+
CREATE FUNCTION pg_stat_statements(
16+
OUT userid oid,
17+
OUT dbid oid,
18+
OUT query text,
19+
OUT calls int8,
20+
OUT total_time float8,
21+
OUT rows int8,
22+
OUT shared_blks_hit int8,
23+
OUT shared_blks_read int8,
24+
OUT shared_blks_dirtied int8,
25+
OUT shared_blks_written int8,
26+
OUT local_blks_hit int8,
27+
OUT local_blks_read int8,
28+
OUT local_blks_dirtied int8,
29+
OUT local_blks_written int8,
30+
OUT temp_blks_read int8,
31+
OUT temp_blks_written int8
32+
)
33+
RETURNS SETOF record
34+
AS 'MODULE_PATHNAME'
35+
LANGUAGE C;
36+
37+
CREATE VIEW pg_stat_statements AS
38+
SELECT * FROM pg_stat_statements();
39+
40+
GRANT SELECT ON pg_stat_statements TO PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements--1.1.sql

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
5+
6+
-- Register functions.
7+
CREATE FUNCTION pg_stat_statements_reset()
8+
RETURNS void
9+
AS 'MODULE_PATHNAME'
10+
LANGUAGE C;
11+
12+
CREATE FUNCTION pg_stat_statements(
13+
OUT userid oid,
14+
OUT dbid oid,
15+
OUT query text,
16+
OUT calls int8,
17+
OUT total_time float8,
18+
OUT rows int8,
19+
OUT shared_blks_hit int8,
20+
OUT shared_blks_read int8,
21+
OUT shared_blks_dirtied int8,
22+
OUT shared_blks_written int8,
23+
OUT local_blks_hit int8,
24+
OUT local_blks_read int8,
25+
OUT local_blks_dirtied int8,
26+
OUT local_blks_written int8,
27+
OUT temp_blks_read int8,
28+
OUT temp_blks_written int8
29+
)
30+
RETURNS SETOF record
31+
AS 'MODULE_PATHNAME'
32+
LANGUAGE C;
33+
34+
-- Register a view on the function for ease of use.
35+
CREATE VIEW pg_stat_statements AS
36+
SELECT * FROM pg_stat_statements();
37+
38+
GRANT SELECT ON pg_stat_statements TO PUBLIC;
39+
40+
-- Don't want this to be available to non-superusers.
41+
REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -77,9 +77,11 @@ typedef struct Counters
7777
int64 rows; /* total # of retrieved or affected rows */
7878
int64 shared_blks_hit; /* # of shared buffer hits */
7979
int64 shared_blks_read; /* # of shared disk blocks read */
80+
int64 shared_blks_dirtied; /* # of shared disk blocks dirtied */
8081
int64 shared_blks_written; /* # of shared disk blocks written */
8182
int64 local_blks_hit; /* # of local buffer hits */
8283
int64 local_blks_read; /* # of local disk blocks read */
84+
int64 local_blks_dirtied; /* # of local disk blocks dirtied */
8385
int64 local_blks_written; /* # of local disk blocks written */
8486
int64 temp_blks_read; /* # of temp blocks read */
8587
int64 temp_blks_written; /* # of temp blocks written */
@@ -652,12 +654,16 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
652654
pgBufferUsage.shared_blks_hit - bufusage.shared_blks_hit;
653655
bufusage.shared_blks_read =
654656
pgBufferUsage.shared_blks_read - bufusage.shared_blks_read;
657+
bufusage.shared_blks_dirtied =
658+
pgBufferUsage.shared_blks_dirtied - bufusage.shared_blks_dirtied;
655659
bufusage.shared_blks_written =
656660
pgBufferUsage.shared_blks_written - bufusage.shared_blks_written;
657661
bufusage.local_blks_hit =
658662
pgBufferUsage.local_blks_hit - bufusage.local_blks_hit;
659663
bufusage.local_blks_read =
660664
pgBufferUsage.local_blks_read - bufusage.local_blks_read;
665+
bufusage.local_blks_dirtied =
666+
pgBufferUsage.local_blks_dirtied - bufusage.local_blks_dirtied;
661667
bufusage.local_blks_written =
662668
pgBufferUsage.local_blks_written - bufusage.local_blks_written;
663669
bufusage.temp_blks_read =
@@ -766,9 +772,11 @@ pgss_store(const char *query, double total_time, uint64 rows,
766772
e->counters.rows += rows;
767773
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
768774
e->counters.shared_blks_read += bufusage->shared_blks_read;
775+
e->counters.shared_blks_dirtied += bufusage->shared_blks_dirtied;
769776
e->counters.shared_blks_written += bufusage->shared_blks_written;
770777
e->counters.local_blks_hit += bufusage->local_blks_hit;
771778
e->counters.local_blks_read += bufusage->local_blks_read;
779+
e->counters.local_blks_dirtied += bufusage->local_blks_dirtied;
772780
e->counters.local_blks_written += bufusage->local_blks_written;
773781
e->counters.temp_blks_read += bufusage->temp_blks_read;
774782
e->counters.temp_blks_written += bufusage->temp_blks_written;
@@ -793,7 +801,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
793801
PG_RETURN_VOID();
794802
}
795803

796-
#define PG_STAT_STATEMENTS_COLS 14
804+
#define PG_STAT_STATEMENTS_COLS_V1_0 14
805+
#define PG_STAT_STATEMENTS_COLS 16
797806

798807
/*
799808
* Retrieve statement statistics.
@@ -810,6 +819,7 @@ pg_stat_statements(PG_FUNCTION_ARGS)
810819
bool is_superuser = superuser();
811820
HASH_SEQ_STATUS hash_seq;
812821
pgssEntry *entry;
822+
bool sql_supports_dirty_counters = true;
813823

814824
if (!pgss || !pgss_hash)
815825
ereport(ERROR,
@@ -830,6 +840,8 @@ pg_stat_statements(PG_FUNCTION_ARGS)
830840
/* Build a tuple descriptor for our result type */
831841
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
832842
elog(ERROR, "return type must be a row type");
843+
if (tupdesc->natts == PG_STAT_STATEMENTS_COLS_V1_0)
844+
sql_supports_dirty_counters = false;
833845

834846
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
835847
oldcontext = MemoryContextSwitchTo(per_query_ctx);
@@ -887,14 +899,19 @@ pg_stat_statements(PG_FUNCTION_ARGS)
887899
values[i++] = Int64GetDatumFast(tmp.rows);
888900
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
889901
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
902+
if (sql_supports_dirty_counters)
903+
values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
890904
values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
891905
values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
892906
values[i++] = Int64GetDatumFast(tmp.local_blks_read);
907+
if (sql_supports_dirty_counters)
908+
values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
893909
values[i++] = Int64GetDatumFast(tmp.local_blks_written);
894910
values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
895911
values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
896912

897-
Assert(i == PG_STAT_STATEMENTS_COLS);
913+
Assert(i == sql_supports_dirty_counters ? \
914+
PG_STAT_STATEMENTS_COLS : PG_STAT_STATEMENTS_COLS_V1_0);
898915

899916
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
900917
}

‎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 execution statistics of all SQL statements executed'
3-
default_version = '1.0'
3+
default_version = '1.1'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,6 +99,13 @@
9999
<entry>Total number of shared blocks reads by the statement</entry>
100100
</row>
101101

102+
<row>
103+
<entry><structfield>shared_blks_dirtied</structfield></entry>
104+
<entry><type>bigint</type></entry>
105+
<entry></entry>
106+
<entry>Total number of shared blocks dirtied by the statement</entry>
107+
</row>
108+
102109
<row>
103110
<entry><structfield>shared_blks_written</structfield></entry>
104111
<entry><type>bigint</type></entry>
@@ -120,6 +127,13 @@
120127
<entry>Total number of local blocks reads by the statement</entry>
121128
</row>
122129

130+
<row>
131+
<entry><structfield>local_blks_dirtied</structfield></entry>
132+
<entry><type>bigint</type></entry>
133+
<entry></entry>
134+
<entry>Total number of local blocks dirtied by the statement</entry>
135+
</row>
136+
123137
<row>
124138
<entry><structfield>local_blks_written</structfield></entry>
125139
<entry><type>bigint</type></entry>

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

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -155,14 +155,20 @@ ROLLBACK;
155155
<listitem>
156156
<para>
157157
Include information on buffer usage. Specifically, include the number of
158-
shared blocks hits, reads, and writes, the number of local blocks hits,
159-
reads, and writes, and the number of temp blocks reads and writes.
160-
A <quote>hit</> means that a read was avoided because the block was
158+
shared blocks hit, read, dirtied, and written, the number of local blocks
159+
hit, read, dirtied, and written, and the number of temp blocks read and
160+
written.
161+
A <emphasis>hit</> means that a read was avoided because the block was
161162
found already in cache when needed.
162163
Shared blocks contain data from regular tables and indexes;
163164
local blocks contain data from temporary tables and indexes;
164165
while temp blocks contain short-term working data used in sorts, hashes,
165166
Materialize plan nodes, and similar cases.
167+
The number of blocks <emphasis>dirtied</> indicates the number of
168+
previously unmodified blocks that were changed by this query; while the
169+
number of blocks <emphasis>written</> indicates the number of
170+
previously-dirtied blocks evicted from cache by this backend during
171+
query processing.
166172
The number of blocks shown for an
167173
upper-level node includes those used by all its child nodes. In text
168174
format, only non-zero values are printed. This parameter may only be

‎src/backend/commands/explain.c

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1183,12 +1183,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
11831183
{
11841184
bool has_shared = (usage->shared_blks_hit > 0 ||
11851185
usage->shared_blks_read > 0 ||
1186-
usage->shared_blks_written);
1186+
usage->shared_blks_dirtied > 0 ||
1187+
usage->shared_blks_written > 0);
11871188
bool has_local = (usage->local_blks_hit > 0 ||
11881189
usage->local_blks_read > 0 ||
1189-
usage->local_blks_written);
1190+
usage->local_blks_dirtied > 0 ||
1191+
usage->local_blks_written > 0);
11901192
bool has_temp = (usage->temp_blks_read > 0 ||
1191-
usage->temp_blks_written);
1193+
usage->temp_blks_written > 0);
11921194

11931195
/* Show only positive counter values. */
11941196
if (has_shared || has_local || has_temp)
@@ -1205,6 +1207,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
12051207
if (usage->shared_blks_read > 0)
12061208
appendStringInfo(es->str, " read=%ld",
12071209
usage->shared_blks_read);
1210+
if (usage->shared_blks_dirtied > 0)
1211+
appendStringInfo(es->str, " dirtied=%ld",
1212+
usage->shared_blks_dirtied);
12081213
if (usage->shared_blks_written > 0)
12091214
appendStringInfo(es->str, " written=%ld",
12101215
usage->shared_blks_written);
@@ -1220,6 +1225,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
12201225
if (usage->local_blks_read > 0)
12211226
appendStringInfo(es->str, " read=%ld",
12221227
usage->local_blks_read);
1228+
if (usage->local_blks_dirtied > 0)
1229+
appendStringInfo(es->str, " dirtied=%ld",
1230+
usage->local_blks_dirtied);
12231231
if (usage->local_blks_written > 0)
12241232
appendStringInfo(es->str, " written=%ld",
12251233
usage->local_blks_written);
@@ -1243,9 +1251,11 @@ ExplainNode(PlanState *planstate, List *ancestors,
12431251
{
12441252
ExplainPropertyLong("Shared Hit Blocks", usage->shared_blks_hit, es);
12451253
ExplainPropertyLong("Shared Read Blocks", usage->shared_blks_read, es);
1254+
ExplainPropertyLong("Shared Dirtied Blocks", usage->shared_blks_dirtied, es);
12461255
ExplainPropertyLong("Shared Written Blocks", usage->shared_blks_written, es);
12471256
ExplainPropertyLong("Local Hit Blocks", usage->local_blks_hit, es);
12481257
ExplainPropertyLong("Local Read Blocks", usage->local_blks_read, es);
1258+
ExplainPropertyLong("Local Dirtied Blocks", usage->local_blks_dirtied, es);
12491259
ExplainPropertyLong("Local Written Blocks", usage->local_blks_written, es);
12501260
ExplainPropertyLong("Temp Read Blocks", usage->temp_blks_read, es);
12511261
ExplainPropertyLong("Temp Written Blocks", usage->temp_blks_written, es);

‎src/backend/executor/instrument.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,9 +137,11 @@ BufferUsageAccumDiff(BufferUsage *dst,
137137
{
138138
dst->shared_blks_hit += add->shared_blks_hit - sub->shared_blks_hit;
139139
dst->shared_blks_read += add->shared_blks_read - sub->shared_blks_read;
140+
dst->shared_blks_dirtied += add->shared_blks_dirtied - sub->shared_blks_dirtied;
140141
dst->shared_blks_written += add->shared_blks_written - sub->shared_blks_written;
141142
dst->local_blks_hit += add->local_blks_hit - sub->local_blks_hit;
142143
dst->local_blks_read += add->local_blks_read - sub->local_blks_read;
144+
dst->local_blks_dirtied += add->local_blks_dirtied - sub->local_blks_dirtied;
143145
dst->local_blks_written += add->local_blks_written - sub->local_blks_written;
144146
dst->temp_blks_read += add->temp_blks_read - sub->temp_blks_read;
145147
dst->temp_blks_written += add->temp_blks_written - sub->temp_blks_written;

‎src/backend/storage/buffer/bufmgr.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -988,6 +988,7 @@ MarkBufferDirty(Buffer buffer)
988988
if (dirtied)
989989
{
990990
VacuumPageDirty++;
991+
pgBufferUsage.shared_blks_dirtied++;
991992
if (VacuumCostActive)
992993
VacuumCostBalance += VacuumCostPageDirty;
993994
if (ProcGlobal->bgwriterLatch)

0 commit comments

Comments
 (0)