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

Commit2254367

Browse files
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 parentf74f9a2 commit2254367

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
ifdefUSE_PGXS
1011
PG_CONFIG = pg_config
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+
DROPVIEW pg_stat_statements;
12+
DROPFUNCTION pg_stat_statements();
13+
14+
/* Now redefine*/
15+
CREATEFUNCTIONpg_stat_statements(
16+
OUT useridoid,
17+
OUT dbidoid,
18+
OUT querytext,
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+
CREATEVIEWpg_stat_statementsAS
38+
SELECT*FROM pg_stat_statements();
39+
40+
GRANTSELECTON pg_stat_statements TO PUBLIC;
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+
CREATEFUNCTIONpg_stat_statements_reset()
8+
RETURNS void
9+
AS'MODULE_PATHNAME'
10+
LANGUAGE C;
11+
12+
CREATEFUNCTIONpg_stat_statements(
13+
OUT useridoid,
14+
OUT dbidoid,
15+
OUT querytext,
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+
CREATEVIEWpg_stat_statementsAS
36+
SELECT*FROM pg_stat_statements();
37+
38+
GRANTSELECTON pg_stat_statements TO PUBLIC;
39+
40+
-- Don't want this to be available to non-superusers.
41+
REVOKE ALLON 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
int64rows;/* total # of retrieved or affected rows */
7878
int64shared_blks_hit;/* # of shared buffer hits */
7979
int64shared_blks_read;/* # of shared disk blocks read */
80+
int64shared_blks_dirtied;/* # of shared disk blocks dirtied */
8081
int64shared_blks_written;/* # of shared disk blocks written */
8182
int64local_blks_hit;/* # of local buffer hits */
8283
int64local_blks_read;/* # of local disk blocks read */
84+
int64local_blks_dirtied;/* # of local disk blocks dirtied */
8385
int64local_blks_written;/* # of local disk blocks written */
8486
int64temp_blks_read;/* # of temp blocks read */
8587
int64temp_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-
#definePG_STAT_STATEMENTS_COLS14
804+
#definePG_STAT_STATEMENTS_COLS_V1_014
805+
#definePG_STAT_STATEMENTS_COLS16
797806

798807
/*
799808
* Retrieve statement statistics.
@@ -810,6 +819,7 @@ pg_stat_statements(PG_FUNCTION_ARGS)
810819
boolis_superuser=superuser();
811820
HASH_SEQ_STATUShash_seq;
812821
pgssEntry*entry;
822+
boolsql_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
}
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
boolhas_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
boolhas_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
boolhas_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)
[8]ページ先頭
©2009-2025 Movatter.jp