On 12/21/2014 02:50 PM, Andrew Dunstan wrote:
On 12/21/2014 02:12 PM, Tom Lane wrote:
Andrew Dunstan <and...@dunslane.net> writes:
On 12/21/2014 01:23 PM, Alvaro Herrera wrote:
The point, I think, is that without atomic instructions you have to
hold
a lock while incrementing the counters.
Hmm, do we do that now?
We already have a spinlock mutex around the counter adjustment code, so
I'm not sure why this discussion is being held.
Because Peter suggested we might be able to use atomics. I'm a bit
dubious that we can for min and max anyway.
I would like someone more versed in numerical analysis than me to
tell me how safe using sum of squares actually is in our case.
That, on the other hand, might be a real issue. I'm afraid that
accumulating across a very long series of statements could lead
to severe roundoff error in the reported values, unless we use
a method chosen for numerical stability.
Right.
The next question along those lines is whether we need to keep a
running mean or whether that can safely be calculated on the fly. The
code at <http://www.johndcook.com/blog/standard_deviation/> does keep
a running mean, and maybe that's required to prevent ill conditioned
results, although I'm quite sure I see how it would. But this isn't my
area of expertise.
I played it safe and kept a running mean, and since it's there and
useful in itself I exposed it via the function, so there are four new
columns, min_time, max_time, mean_time and stddev_time.
I'll add this to the upcoming commitfest.
cheers
andrew
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 2709909..975a637 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,8 +4,9 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o $(WIN32RES)
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.2.sql pg_stat_statements--1.1--1.2.sql \
- pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.3.sql pg_stat_statements--1.2--1.3.sql \
+ pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--unpackaged--1.0.sql
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
ifdef USE_PGXS
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.2-1.3.sql b/contrib/pg_stat_statements/pg_stat_statements--1.2-1.3.sql
new file mode 100644
index 0000000..506959d
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.2-1.3.sql
@@ -0,0 +1,47 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.3'" 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(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT mean_time float8,
+ OUT stddev_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', 'pg_stat_statements_1_3'
+LANGUAGE C STRICT VOLATILE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
deleted file mode 100644
index 5bfa9a5..0000000
--- a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
+++ /dev/null
@@ -1,44 +0,0 @@
-/* contrib/pg_stat_statements/pg_stat_statements--1.2.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(IN showtext boolean,
- OUT userid oid,
- OUT dbid oid,
- OUT queryid bigint,
- 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', 'pg_stat_statements_1_2'
-LANGUAGE C STRICT VOLATILE;
-
--- Register a view on the function for ease of use.
-CREATE VIEW pg_stat_statements AS
- SELECT * FROM pg_stat_statements(true);
-
-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--1.3.sql b/contrib/pg_stat_statements/pg_stat_statements--1.3.sql
new file mode 100644
index 0000000..92ed057
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.3.sql
@@ -0,0 +1,48 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.3.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(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT mean_time float8,
+ OUT stddev_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', 'pg_stat_statements_1_3'
+LANGUAGE C STRICT VOLATILE;
+
+-- Register a view on the function for ease of use.
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+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 2629bfc..76760e7 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -115,7 +115,8 @@ typedef enum pgssVersion
{
PGSS_V1_0 = 0,
PGSS_V1_1,
- PGSS_V1_2
+ PGSS_V1_2,
+ PGSS_V1_3
} pgssVersion;
/*
@@ -136,6 +137,10 @@ typedef struct Counters
{
int64 calls; /* # of times executed */
double total_time; /* total execution time, in msec */
+ double min_time; /* minimim execution time in msec */
+ double max_time; /* maximum execution time in msec */
+ double mean_time; /* mean execution time in msec */
+ double sum_var_time; /* sum of variances in 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,6 +279,7 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
PG_FUNCTION_INFO_V1(pg_stat_statements);
static void pgss_shmem_startup(void);
@@ -320,6 +326,7 @@ 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);
+static inline double sqrtd(const double x);
/*
@@ -1215,6 +1222,32 @@ pgss_store(const char *query, uint32 queryId,
e->counters.calls += 1;
e->counters.total_time += total_time;
+ if (e->counters.calls == 1)
+ {
+ e->counters.min_time = total_time;
+ e->counters.max_time = total_time;
+ e->counters.mean_time = total_time;
+ }
+ else
+ {
+ /*
+ * Welford's method for accurately computing variance.
+ * See <http://www.johndcook.com/blog/standard_deviation/>
+ */
+ double old_mean = e->counters.mean_time;
+
+ e->counters.mean_time +=
+ (total_time - old_mean) / e->counters.calls;
+ e->counters.sum_var_time +=
+ (total_time - old_mean) * (total_time - e->counters.mean_time);
+
+ /* calculate min and max time */
+ if (e->counters.min_time > total_time)
+ e->counters.min_time = total_time;
+ if (e->counters.max_time < total_time)
+ e->counters.max_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;
@@ -1259,7 +1292,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#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 */
+#define PG_STAT_STATEMENTS_COLS_V1_3 23
+#define PG_STAT_STATEMENTS_COLS 23 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1272,6 +1306,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
Datum
+pg_stat_statements_1_3(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_3, showtext);
+
+ return (Datum) 0;
+}
+
+Datum
pg_stat_statements_1_2(PG_FUNCTION_ARGS)
{
bool showtext = PG_GETARG_BOOL(0);
@@ -1360,6 +1404,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_2)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_3:
+ if (api_version != PGSS_V1_3)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1519,6 +1567,17 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (api_version >= PGSS_V1_3)
+ {
+ values[i++] = Float8GetDatumFast(tmp.min_time);
+ values[i++] = Float8GetDatumFast(tmp.max_time);
+ values[i++] = Float8GetDatumFast(tmp.mean_time);
+ if (tmp.calls > 1)
+ values[i++] =
+ Float8GetDatumFast(sqrt(tmp.sum_var_time / (tmp.calls-1)));
+ else
+ values[i++] = Float8GetDatumFast(0.0);
+ }
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
@@ -1541,6 +1600,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
+ api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -2896,3 +2956,20 @@ comp_location(const void *a, const void *b)
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;
+}
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 6ecf2b6..53df978 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.2'
+default_version = '1.3'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index a58ecf7..428f77a 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -87,6 +87,34 @@
</row>
<row>
+ <entry><structfield>min_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Minimum 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 time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
+ <entry><structfield>mean_time</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Mean 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 time spent in the statement, in milliseconds</entry>
+ </row>
+
+ <row>
<entry><structfield>rows</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
