Rebased patch is attached.
pg_stat_statements in PG9.4dev has already changed table columns in. So I hope
this patch will be committed in PG9.4dev.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
*** a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
***************
*** 19,24 **** CREATE FUNCTION pg_stat_statements(
--- 19,27 ----
OUT query text,
OUT calls int8,
OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT stdev_time float8,
OUT rows int8,
OUT shared_blks_hit int8,
OUT shared_blks_read int8,
***************
*** 41,43 **** CREATE VIEW pg_stat_statements AS
--- 44,52 ----
SELECT * FROM pg_stat_statements();
GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+ /* New Function */
+ CREATE FUNCTION pg_stat_statements_reset_time()
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
*** a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
***************
*** 9,14 **** RETURNS void
--- 9,19 ----
AS 'MODULE_PATHNAME'
LANGUAGE C;
+ CREATE FUNCTION pg_stat_statements_reset_time()
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
+
CREATE FUNCTION pg_stat_statements(
OUT userid oid,
OUT dbid oid,
***************
*** 16,21 **** CREATE FUNCTION pg_stat_statements(
--- 21,29 ----
OUT query text,
OUT calls int8,
OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT stdev_time float8,
OUT rows int8,
OUT shared_blks_hit int8,
OUT shared_blks_read int8,
***************
*** 42,44 **** GRANT SELECT ON pg_stat_statements TO PUBLIC;
--- 50,53 ----
-- Don't want this to be available to non-superusers.
REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
+ REVOKE ALL ON FUNCTION pg_stat_statements_reset_time() FROM PUBLIC;
*** 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,8 ****
--- 4,9 ----
\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;
*** a/contrib/pg_stat_statements/pg_stat_statements.c
--- b/contrib/pg_stat_statements/pg_stat_statements.c
***************
*** 78,83 **** static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
--- 78,84 ----
#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 */
***************
*** 114,119 **** typedef struct Counters
--- 115,123 ----
{
int64 calls; /* # of times executed */
double total_time; /* total execution time, in msec */
+ double total_sqtime; /* cumulated square 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 */
***************
*** 237,245 **** void _PG_init(void);
--- 241,251 ----
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);
***************
*** 266,271 **** static pgssEntry *entry_alloc(pgssHashKey *key, const char *query,
--- 272,278 ----
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);
***************
*** 1046,1051 **** pgss_store(const char *query, uint32 queryId,
--- 1053,1059 ----
e->counters.calls += 1;
e->counters.total_time += total_time;
+ e->counters.total_sqtime += total_time * total_time;
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
e->counters.shared_blks_read += bufusage->shared_blks_read;
***************
*** 1061,1066 **** pgss_store(const char *query, uint32 queryId,
--- 1069,1079 ----
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);
}
***************
*** 1085,1093 **** pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS 19
/*
* Retrieve statement statistics.
--- 1098,1120 ----
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_V1_1 18
! #define PG_STAT_STATEMENTS_COLS 22
/*
* Retrieve statement statistics.
***************
*** 1207,1212 **** pg_stat_statements(PG_FUNCTION_ARGS)
--- 1234,1255 ----
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (detected_version >= PGSS_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);
+ {
+ double avtime = tmp.total_time /tmp.calls;
+ double sqtime = tmp.total_sqtime /tmp.calls;
+ values[i++] = Float8GetDatumFast(sqrt(sqtime - avtime * avtime));
+ }
+ }
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
***************
*** 1304,1309 **** entry_alloc(pgssHashKey *key, const char *query, int query_len, bool sticky)
--- 1347,1355 ----
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;
***************
*** 1397,1402 **** entry_reset(void)
--- 1443,1469 ----
}
/*
+ * 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.
*/
*** a/doc/src/sgml/pgstatstatements.sgml
--- b/doc/src/sgml/pgstatstatements.sgml
***************
*** 83,89 ****
<entry><structfield>total_time</structfield></entry>
<entry><type>double precision</type></entry>
<entry></entry>
! <entry>Total time spent in the statement, in milliseconds</entry>
</row>
<row>
--- 83,110 ----
<entry><structfield>total_time</structfield></entry>
<entry><type>double precision</type></entry>
<entry></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>
! <entry><structfield>stddev_time</structfield></entry>
! <entry><type>double precision</type></entry>
! <entry></entry>
! <entry>Standard deviation of the execution time spent in the statement</entry>
</row>
<row>
***************
*** 297,302 ****
--- 318,341 ----
</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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
