aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/pg_stat_statements/Makefile3
-rw-r--r--contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql40
-rw-r--r--contrib/pg_stat_statements/pg_stat_statements--1.1.sql41
-rw-r--r--contrib/pg_stat_statements/pg_stat_statements.c21
-rw-r--r--contrib/pg_stat_statements/pg_stat_statements.control2
-rw-r--r--doc/src/sgml/pgstatstatements.sgml14
-rw-r--r--doc/src/sgml/ref/explain.sgml12
-rw-r--r--src/backend/commands/explain.c16
-rw-r--r--src/backend/executor/instrument.c2
-rw-r--r--src/backend/storage/buffer/bufmgr.c1
-rw-r--r--src/backend/storage/buffer/localbuf.c4
-rw-r--r--src/include/executor/instrument.h2
12 files changed, 148 insertions, 10 deletions
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index e086fd8a827..e8aed612164 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,7 +4,8 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.0.sql pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
new file mode 100644
index 00000000000..223271d0ce5
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
@@ -0,0 +1,40 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements();
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements();
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements();
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
new file mode 100644
index 00000000000..1233736c9c4
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
@@ -0,0 +1,41 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
+
+-- Register functions.
+CREATE FUNCTION pg_stat_statements_reset()
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+-- Register a view on the function for ease of use.
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements();
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+-- Don't want this to be available to non-superusers.
+REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 434aa71a33c..914fbf270d7 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -77,9 +77,11 @@ typedef struct Counters
int64 rows; /* total # of retrieved or affected rows */
int64 shared_blks_hit; /* # of shared buffer hits */
int64 shared_blks_read; /* # of shared disk blocks read */
+ int64 shared_blks_dirtied; /* # of shared disk blocks dirtied */
int64 shared_blks_written; /* # of shared disk blocks written */
int64 local_blks_hit; /* # of local buffer hits */
int64 local_blks_read; /* # of local disk blocks read */
+ int64 local_blks_dirtied; /* # of local disk blocks dirtied */
int64 local_blks_written; /* # of local disk blocks written */
int64 temp_blks_read; /* # of temp blocks read */
int64 temp_blks_written; /* # of temp blocks written */
@@ -652,12 +654,16 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
pgBufferUsage.shared_blks_hit - bufusage.shared_blks_hit;
bufusage.shared_blks_read =
pgBufferUsage.shared_blks_read - bufusage.shared_blks_read;
+ bufusage.shared_blks_dirtied =
+ pgBufferUsage.shared_blks_dirtied - bufusage.shared_blks_dirtied;
bufusage.shared_blks_written =
pgBufferUsage.shared_blks_written - bufusage.shared_blks_written;
bufusage.local_blks_hit =
pgBufferUsage.local_blks_hit - bufusage.local_blks_hit;
bufusage.local_blks_read =
pgBufferUsage.local_blks_read - bufusage.local_blks_read;
+ bufusage.local_blks_dirtied =
+ pgBufferUsage.local_blks_dirtied - bufusage.local_blks_dirtied;
bufusage.local_blks_written =
pgBufferUsage.local_blks_written - bufusage.local_blks_written;
bufusage.temp_blks_read =
@@ -766,9 +772,11 @@ pgss_store(const char *query, double total_time, uint64 rows,
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
e->counters.shared_blks_read += bufusage->shared_blks_read;
+ e->counters.shared_blks_dirtied += bufusage->shared_blks_dirtied;
e->counters.shared_blks_written += bufusage->shared_blks_written;
e->counters.local_blks_hit += bufusage->local_blks_hit;
e->counters.local_blks_read += bufusage->local_blks_read;
+ e->counters.local_blks_dirtied += bufusage->local_blks_dirtied;
e->counters.local_blks_written += bufusage->local_blks_written;
e->counters.temp_blks_read += bufusage->temp_blks_read;
e->counters.temp_blks_written += bufusage->temp_blks_written;
@@ -793,7 +801,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
-#define PG_STAT_STATEMENTS_COLS 14
+#define PG_STAT_STATEMENTS_COLS_V1_0 14
+#define PG_STAT_STATEMENTS_COLS 16
/*
* Retrieve statement statistics.
@@ -810,6 +819,7 @@ pg_stat_statements(PG_FUNCTION_ARGS)
bool is_superuser = superuser();
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
+ bool sql_supports_dirty_counters = true;
if (!pgss || !pgss_hash)
ereport(ERROR,
@@ -830,6 +840,8 @@ pg_stat_statements(PG_FUNCTION_ARGS)
/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
+ if (tupdesc->natts == PG_STAT_STATEMENTS_COLS_V1_0)
+ sql_supports_dirty_counters = false;
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
@@ -887,14 +899,19 @@ pg_stat_statements(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
+ if (sql_supports_dirty_counters)
+ values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
values[i++] = Int64GetDatumFast(tmp.local_blks_read);
+ if (sql_supports_dirty_counters)
+ values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.local_blks_written);
values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
- Assert(i == PG_STAT_STATEMENTS_COLS);
+ Assert(i == sql_supports_dirty_counters ? \
+ PG_STAT_STATEMENTS_COLS : PG_STAT_STATEMENTS_COLS_V1_0);
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 6f9a9471228..428fbb23749 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 5a0230c4286..ab34ca193a4 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -100,6 +100,13 @@
</row>
<row>
+ <entry><structfield>shared_blks_dirtied</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Total number of shared blocks dirtied by the statement</entry>
+ </row>
+
+ <row>
<entry><structfield>shared_blks_written</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
@@ -121,6 +128,13 @@
</row>
<row>
+ <entry><structfield>local_blks_dirtied</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Total number of local blocks dirtied by the statement</entry>
+ </row>
+
+ <row>
<entry><structfield>local_blks_written</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 419b72cad34..1f35a1d1552 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -155,14 +155,20 @@ ROLLBACK;
<listitem>
<para>
Include information on buffer usage. Specifically, include the number of
- shared blocks hits, reads, and writes, the number of local blocks hits,
- reads, and writes, and the number of temp blocks reads and writes.
- A <quote>hit</> means that a read was avoided because the block was
+ shared blocks hit, read, dirtied, and written, the number of local blocks
+ hit, read, dirtied, and written, and the number of temp blocks read and
+ written.
+ A <emphasis>hit</> means that a read was avoided because the block was
found already in cache when needed.
Shared blocks contain data from regular tables and indexes;
local blocks contain data from temporary tables and indexes;
while temp blocks contain short-term working data used in sorts, hashes,
Materialize plan nodes, and similar cases.
+ The number of blocks <emphasis>dirtied</> indicates the number of
+ previously unmodified blocks that were changed by this query; while the
+ number of blocks <emphasis>written</> indicates the number of
+ previously-dirtied blocks evicted from cache by this backend during
+ query processing.
The number of blocks shown for an
upper-level node includes those used by all its child nodes. In text
format, only non-zero values are printed. This parameter may only be
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a1692f82ae8..93b1f34ca0c 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1183,12 +1183,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
{
bool has_shared = (usage->shared_blks_hit > 0 ||
usage->shared_blks_read > 0 ||
- usage->shared_blks_written);
+ usage->shared_blks_dirtied > 0 ||
+ usage->shared_blks_written > 0);
bool has_local = (usage->local_blks_hit > 0 ||
usage->local_blks_read > 0 ||
- usage->local_blks_written);
+ usage->local_blks_dirtied > 0 ||
+ usage->local_blks_written > 0);
bool has_temp = (usage->temp_blks_read > 0 ||
- usage->temp_blks_written);
+ usage->temp_blks_written > 0);
/* Show only positive counter values. */
if (has_shared || has_local || has_temp)
@@ -1205,6 +1207,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
if (usage->shared_blks_read > 0)
appendStringInfo(es->str, " read=%ld",
usage->shared_blks_read);
+ if (usage->shared_blks_dirtied > 0)
+ appendStringInfo(es->str, " dirtied=%ld",
+ usage->shared_blks_dirtied);
if (usage->shared_blks_written > 0)
appendStringInfo(es->str, " written=%ld",
usage->shared_blks_written);
@@ -1220,6 +1225,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
if (usage->local_blks_read > 0)
appendStringInfo(es->str, " read=%ld",
usage->local_blks_read);
+ if (usage->local_blks_dirtied > 0)
+ appendStringInfo(es->str, " dirtied=%ld",
+ usage->local_blks_dirtied);
if (usage->local_blks_written > 0)
appendStringInfo(es->str, " written=%ld",
usage->local_blks_written);
@@ -1243,9 +1251,11 @@ ExplainNode(PlanState *planstate, List *ancestors,
{
ExplainPropertyLong("Shared Hit Blocks", usage->shared_blks_hit, es);
ExplainPropertyLong("Shared Read Blocks", usage->shared_blks_read, es);
+ ExplainPropertyLong("Shared Dirtied Blocks", usage->shared_blks_dirtied, es);
ExplainPropertyLong("Shared Written Blocks", usage->shared_blks_written, es);
ExplainPropertyLong("Local Hit Blocks", usage->local_blks_hit, es);
ExplainPropertyLong("Local Read Blocks", usage->local_blks_read, es);
+ ExplainPropertyLong("Local Dirtied Blocks", usage->local_blks_dirtied, es);
ExplainPropertyLong("Local Written Blocks", usage->local_blks_written, es);
ExplainPropertyLong("Temp Read Blocks", usage->temp_blks_read, es);
ExplainPropertyLong("Temp Written Blocks", usage->temp_blks_written, es);
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index 2c749b13cd8..6e9f450d688 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -137,9 +137,11 @@ BufferUsageAccumDiff(BufferUsage *dst,
{
dst->shared_blks_hit += add->shared_blks_hit - sub->shared_blks_hit;
dst->shared_blks_read += add->shared_blks_read - sub->shared_blks_read;
+ dst->shared_blks_dirtied += add->shared_blks_dirtied - sub->shared_blks_dirtied;
dst->shared_blks_written += add->shared_blks_written - sub->shared_blks_written;
dst->local_blks_hit += add->local_blks_hit - sub->local_blks_hit;
dst->local_blks_read += add->local_blks_read - sub->local_blks_read;
+ dst->local_blks_dirtied += add->local_blks_dirtied - sub->local_blks_dirtied;
dst->local_blks_written += add->local_blks_written - sub->local_blks_written;
dst->temp_blks_read += add->temp_blks_read - sub->temp_blks_read;
dst->temp_blks_written += add->temp_blks_written - sub->temp_blks_written;
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 1adb6d360dd..3924a51c0c6 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -988,6 +988,7 @@ MarkBufferDirty(Buffer buffer)
if (dirtied)
{
VacuumPageDirty++;
+ pgBufferUsage.shared_blks_dirtied++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageDirty;
if (ProcGlobal->bgwriterLatch)
diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c
index 096d36a233b..63c14f7300c 100644
--- a/src/backend/storage/buffer/localbuf.c
+++ b/src/backend/storage/buffer/localbuf.c
@@ -276,6 +276,10 @@ MarkLocalBufferDirty(Buffer buffer)
Assert(LocalRefCount[bufid] > 0);
bufHdr = &LocalBufferDescriptors[bufid];
+
+ if (!(bufHdr->flags & BM_DIRTY))
+ pgBufferUsage.local_blks_dirtied++;
+
bufHdr->flags |= BM_DIRTY;
}
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index 084302e4e7e..066f684f330 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -20,9 +20,11 @@ typedef struct BufferUsage
{
long shared_blks_hit; /* # of shared buffer hits */
long shared_blks_read; /* # of shared disk blocks read */
+ long shared_blks_dirtied; /* # of shared blocks dirtied */
long shared_blks_written; /* # of shared disk blocks written */
long local_blks_hit; /* # of local buffer hits */
long local_blks_read; /* # of local disk blocks read */
+ long local_blks_dirtied; /* # of shared blocks dirtied */
long local_blks_written; /* # of local disk blocks written */
long temp_blks_read; /* # of temp blocks read */
long temp_blks_written; /* # of temp blocks written */
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 04:57:04 +0000