I submit patch adding min and max execute statement time in pg_stat_statement in
next CF.
pg_stat_statement have execution time, but it is average execution time and does
not provide detail information very much. So I add min and max execute statement
time in pg_stat_statement columns. Usage is almost same as before. However, I
add
pg_stat_statements_reset_time() function to get min_time and max_time in the
specific period. This function resets or inits min and max execution time
before.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index e8aed61..5c63940 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,8 +4,10 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
- pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.2.sql \
+ pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--1.1--1.2.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
index 5be281e..5662273 100644
--- 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
@@ -1,7 +1,7 @@
/* 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 '1.1'" to load this file. \quit
+\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;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
deleted file mode 100644
index 42e4d68..0000000
--- a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
+++ /dev/null
@@ -1,43 +0,0 @@
-/* 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,
- OUT blk_read_time float8,
- OUT blk_write_time float8
-)
-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--unpackaged--1.0.sql b/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
index e84a3cb..0addba0 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--unpackaged--1.0.sql
@@ -4,5 +4,6 @@
\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset();
+ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements_reset_time();
ALTER EXTENSION pg_stat_statements ADD function pg_stat_statements();
ALTER EXTENSION pg_stat_statements ADD view pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ea930af..8f9b641 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -76,6 +76,7 @@ static const uint32 PGSS_FILE_HEADER = 0x20120328;
#define USAGE_DECREASE_FACTOR (0.99) /* decreased every entry_dealloc */
#define STICKY_DECREASE_FACTOR (0.50) /* factor for sticky entries */
#define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */
+#define EXEC_TIME_INIT (-1) /* initial execution time */
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
@@ -102,6 +103,8 @@ typedef struct Counters
{
int64 calls; /* # of times executed */
double total_time; /* total execution time, in msec */
+ double min_time; /* maximum execution time, in msec */
+ double max_time; /* minimum execution time, in msec */
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 */
@@ -225,9 +228,11 @@ void _PG_init(void);
void _PG_fini(void);
Datum pg_stat_statements_reset(PG_FUNCTION_ARGS);
+Datum pg_stat_statements_reset_time(PG_FUNCTION_ARGS);
Datum pg_stat_statements(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
+PG_FUNCTION_INFO_V1(pg_stat_statements_reset_time);
PG_FUNCTION_INFO_V1(pg_stat_statements);
static void pgss_shmem_startup(void);
@@ -254,6 +259,7 @@ static pgssEntry *entry_alloc(pgssHashKey *key, const char *query,
int query_len, bool sticky);
static void entry_dealloc(void);
static void entry_reset(void);
+static void entry_reset_time(void);
static void AppendJumble(pgssJumbleState *jstate,
const unsigned char *item, Size size);
static void JumbleQuery(pgssJumbleState *jstate, Query *query);
@@ -1044,6 +1050,11 @@ pgss_store(const char *query, uint32 queryId,
e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
e->counters.usage += USAGE_EXEC(total_time);
+ if (e->counters.min_time > total_time || e->counters.min_time == EXEC_TIME_INIT)
+ e->counters.min_time = total_time;
+ if (e->counters.max_time < total_time)
+ e->counters.max_time = total_time;
+
SpinLockRelease(&e->mutex);
}
@@ -1068,8 +1079,30 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+/*
+ * Reset min/max time statement statistics.
+ */
+Datum
+pg_stat_statements_reset_time(PG_FUNCTION_ARGS)
+{
+ if (!pgss || !pgss_hash)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
+ entry_reset_time();
+ PG_RETURN_VOID();
+}
+
#define PG_STAT_STATEMENTS_COLS_V1_0 14
-#define PG_STAT_STATEMENTS_COLS 18
+#define PG_STAT_STATEMENTS_COLS_V1_1 18
+#define PG_STAT_STATEMENTS_COLS 20
+
+enum SQL_SUPPORT_VERSION
+{
+ SQL_SUPPORTS_V1_0,
+ SQL_SUPPORTS_V1_1,
+ SQL_SUPPORTS_V1_2
+};
/*
* Retrieve statement statistics.
@@ -1086,7 +1119,7 @@ pg_stat_statements(PG_FUNCTION_ARGS)
bool is_superuser = superuser();
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
- bool sql_supports_v1_1_counters = true;
+ int sql_support_version = -1;
if (!pgss || !pgss_hash)
ereport(ERROR,
@@ -1107,8 +1140,21 @@ 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_v1_1_counters = false;
+ switch (tupdesc->natts)
+ {
+ case PG_STAT_STATEMENTS_COLS_V1_0:
+ sql_support_version = SQL_SUPPORTS_V1_0;
+ break;
+ case PG_STAT_STATEMENTS_COLS_V1_1:
+ sql_support_version = SQL_SUPPORTS_V1_1;
+ break;
+ case PG_STAT_STATEMENTS_COLS:
+ sql_support_version = SQL_SUPPORTS_V1_2;
+ break;
+ default:
+ Assert(false); /* can't get here */
+ break;
+ };
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
@@ -1167,28 +1213,36 @@ pg_stat_statements(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (sql_support_version >= SQL_SUPPORTS_V1_2)
+ {
+ if (tmp.min_time == EXEC_TIME_INIT)
+ nulls[i++] = true;
+ else
+ values[i++] = Float8GetDatumFast(tmp.min_time);
+ if (tmp.max_time == EXEC_TIME_INIT)
+ nulls[i++] = true;
+ else
+ values[i++] = Float8GetDatumFast(tmp.max_time);
+ }
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
- if (sql_supports_v1_1_counters)
+ if (sql_support_version >= SQL_SUPPORTS_V1_1)
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_v1_1_counters)
+ if (sql_support_version >= SQL_SUPPORTS_V1_1)
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);
- if (sql_supports_v1_1_counters)
+ if (sql_support_version >= SQL_SUPPORTS_V1_1)
{
values[i++] = Float8GetDatumFast(tmp.blk_read_time);
values[i++] = Float8GetDatumFast(tmp.blk_write_time);
}
- Assert(i == (sql_supports_v1_1_counters ?
- PG_STAT_STATEMENTS_COLS : PG_STAT_STATEMENTS_COLS_V1_0));
-
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
@@ -1261,6 +1315,9 @@ entry_alloc(pgssHashKey *key, const char *query, int query_len, bool sticky)
entry->query_len = query_len;
memcpy(entry->query, query, query_len);
entry->query[query_len] = '\0';
+ /* set the appropriate initial max/min execution time */
+ entry->counters.min_time = EXEC_TIME_INIT;
+ entry->counters.max_time = EXEC_TIME_INIT;
}
return entry;
@@ -1354,6 +1411,27 @@ entry_reset(void)
}
/*
+ * Reset min/max time values of all entries.
+ */
+static void
+entry_reset_time(void)
+{
+ HASH_SEQ_STATUS hash_seq;
+ pgssEntry *entry;
+
+ LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
+
+ hash_seq_init(&hash_seq, pgss_hash);
+ while ((entry = hash_seq_search(&hash_seq)) != NULL)
+ {
+ entry->counters.min_time = EXEC_TIME_INIT;
+ entry->counters.max_time = EXEC_TIME_INIT;
+ }
+
+ LWLockRelease(pgss->lock);
+}
+
+/*
* AppendJumble: Append a value that is substantive in a given query to
* the current jumble.
*/
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 428fbb2..6ecf2b6 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.1'
+default_version = '1.2'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index c02fdf4..2ee5258 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -75,7 +75,21 @@
<entry><structfield>total_time</structfield></entry>
<entry><type>double precision</type></entry>
<entry></entry>
- <entry>Total time spent in the statement, in milliseconds</entry>
+ <entry>Total execution time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
+ <entry><structfield>min_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Minimum execution time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
+ <entry><structfield>max_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Maximum execution time spent in the statement, in milliseconds</entry>
</row>
<row>
@@ -252,6 +266,24 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <indexterm>
+ <primary>pg_stat_statements_reset_time</primary>
+ </indexterm>
+
+ <term>
+ <function>pg_stat_statements_reset_time() returns void</function>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pg_stat_statements_reset_time</function> inits statistics of
+ the min_time and max_time gathered so far by <filename>pg_stat_statements</>.
+ By default, this function can only be executed by superusers.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
