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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers