*** 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(IN showtext boolean, --- 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,51 ---- SELECT * FROM pg_stat_statements(true); GRANT SELECT ON pg_stat_statements TO PUBLIC; + + 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(IN showtext boolean, OUT userid oid, OUT dbid oid, *************** *** 16,21 **** CREATE FUNCTION pg_stat_statements(IN showtext boolean, --- 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 *************** *** 106,111 **** static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100; --- 106,113 ---- #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_MIN DBL_MAX /* initial execution min time */ + #define EXEC_TIME_INIT_MAX -DBL_MAX /* initial execution max time */ #define JUMBLE_SIZE 1024 /* query serialization buffer size */ *************** *** 137,142 **** typedef struct Counters --- 139,147 ---- { 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 */ *************** *** 274,283 **** void _PG_init(void); --- 279,290 ---- 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_1_2(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_1_2); PG_FUNCTION_INFO_V1(pg_stat_statements); *************** *** 315,320 **** static char *qtext_fetch(Size query_offset, int query_len, --- 322,328 ---- static bool need_gc_qtexts(void); static void gc_qtexts(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); *************** *** 325,331 **** static char *generate_normalized_query(pgssJumbleState *jstate, const char *quer int *query_len_p, int encoding); static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query); static int comp_location(const void *a, const void *b); ! /* * Module load callback --- 333,339 ---- int *query_len_p, int encoding); static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query); static int comp_location(const void *a, const void *b); ! static double sqrtd(const double x); /* * Module load callback *************** *** 1107,1112 **** pgss_store(const char *query, uint32 queryId, --- 1115,1123 ---- char *norm_query = NULL; int encoding = GetDatabaseEncoding(); int query_len; + double total_sqtime = total_time * total_time; + double min_time; + double max_time; Assert(query != NULL); *************** *** 1200,1211 **** pgss_store(const char *query, uint32 queryId, --- 1211,1235 ---- SpinLockAcquire(&e->mutex); + /* caluculate min and max time */ + if (e->counters.min_time > total_time) + min_time = total_time; + else + min_time = total_time; + if (e->counters.max_time < total_time) + max_time = total_time; + else + max_time = e->counters.max_time; + /* "Unstick" entry if it was previously sticky */ if (e->counters.calls == 0) e->counters.usage = USAGE_INIT; e->counters.calls += 1; e->counters.total_time += total_time; + e->counters.total_sqtime += total_sqtime; + e->counters.min_time = min_time; + e->counters.max_time = max_time; e->counters.rows += rows; e->counters.shared_blks_hit += bufusage->shared_blks_hit; e->counters.shared_blks_read += bufusage->shared_blks_read; *************** *** 1246,1256 **** pg_stat_statements_reset(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } /* Number of output arguments (columns) for various API versions */ #define PG_STAT_STATEMENTS_COLS_V1_0 14 #define PG_STAT_STATEMENTS_COLS_V1_1 18 ! #define PG_STAT_STATEMENTS_COLS_V1_2 19 ! #define PG_STAT_STATEMENTS_COLS 19 /* maximum of above */ /* * Retrieve statement statistics. --- 1270,1295 ---- 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(); + } + /* Number of output arguments (columns) for various API versions */ #define PG_STAT_STATEMENTS_COLS_V1_0 14 #define PG_STAT_STATEMENTS_COLS_V1_1 18 ! #define PG_STAT_STATEMENTS_COLS_V1_2 22 ! #define PG_STAT_STATEMENTS_COLS 22 /* maximum of above */ /* * Retrieve statement statistics. *************** *** 1435,1440 **** pg_stat_statements_internal(FunctionCallInfo fcinfo, --- 1474,1482 ---- int i = 0; Counters tmp; int64 queryid = entry->key.queryid; + double avtime; + double sqtime; + double stdev; memset(values, 0, sizeof(values)); memset(nulls, 0, sizeof(nulls)); *************** *** 1512,1517 **** pg_stat_statements_internal(FunctionCallInfo fcinfo, --- 1554,1574 ---- values[i++] = Int64GetDatumFast(tmp.calls); values[i++] = Float8GetDatumFast(tmp.total_time); + if (api_version >= PGSS_V1_2) + { + if (tmp.min_time == EXEC_TIME_INIT_MIN) + nulls[i++] = true; + else + values[i++] = Float8GetDatumFast(tmp.min_time); + if (tmp.max_time == EXEC_TIME_INIT_MAX) + nulls[i++] = true; + else + values[i++] = Float8GetDatumFast(tmp.max_time); + avtime = tmp.total_time / tmp.calls; + sqtime = tmp.total_sqtime / tmp.calls; + stdev = (sqrtd(sqtime - avtime * avtime)); + values[i++] = Float8GetDatumFast(stdev); + } values[i++] = Int64GetDatumFast(tmp.rows); values[i++] = Int64GetDatumFast(tmp.shared_blks_hit); values[i++] = Int64GetDatumFast(tmp.shared_blks_read); *************** *** 1597,1602 **** entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding, --- 1654,1663 ---- { /* New entry, initialize it */ + /* set the appropriate initial max/min execution time */ + entry->counters.min_time = EXEC_TIME_INIT_MIN; + entry->counters.max_time = EXEC_TIME_INIT_MAX; + /* reset the statistics */ memset(&entry->counters, 0, sizeof(Counters)); /* set the appropriate initial usage count */ *************** *** 2125,2130 **** done: --- 2186,2211 ---- LWLockRelease(pgss->lock); } + /* + * 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_MIN; + entry->counters.max_time = EXEC_TIME_INIT_MAX; + } + + LWLockRelease(pgss->lock); + } /* * AppendJumble: Append a value that is substantive in a given query to *************** *** 2888,2890 **** comp_location(const void *a, const void *b) --- 2969,2988 ---- else return 0; } + + /* + * fast sqrt algorithm: reference from Fast inverse square root algorithms. + */ + static inline double + sqrtd(const double x) + { + double x_half = 0.5 * x; + long long int tmp = 0x5FE6EB50C7B537AAl - ( *(long long int*)&x >> 1); + double x_result = * (double*)&tmp; + + x_result *= (1.5 - (x_half * x_result * x_result)); + /* If retry this calculation, it becomes higher precision at sqrt */ + x_result *= (1.5 - (x_half * x_result * x_result)); + + return x_result * x; + } *** a/doc/src/sgml/pgstatstatements.sgml --- b/doc/src/sgml/pgstatstatements.sgml *************** *** 83,92 **** total_time double precision ! Total time spent in the statement, in milliseconds rows bigint --- 83,113 ---- total_time double precision ! Total execution time spent in the statement, in milliseconds + min_time + double precision + + Minimum execution time spent in the statement, in milliseconds + + + + max_time + double precision + + Maximum execution time spent in the statement, in milliseconds + + + + stddev_time + double precision + + Standard deviation of the execution time spent in the statement + + + rows bigint *************** *** 298,303 **** --- 319,342 ---- + pg_stat_statements_reset_time + + + + pg_stat_statements_reset_time() returns void + + + + + pg_stat_statements_reset_time inits statistics of + the min_time and max_time gathered so far by pg_stat_statements. + By default, this function can only be executed by superusers. + + + + + + pg_stat_statements function