On 02/16/2015 09:05 PM, Petr Jelinek wrote:
On 17/02/15 02:57, Andrew Dunstan wrote:
On 02/16/2015 08:48 PM, Petr Jelinek wrote:
On 17/02/15 01:57, Peter Geoghegan wrote:
On Mon, Feb 16, 2015 at 4:44 PM, Petr Jelinek <p...@2ndquadrant.com>
wrote:
We definitely want this feature, I wished to have this info many
times.
I would still like to see a benchmark.
Average of 3 runs of read-only pgbench on my system all with
pg_stat_statement activated:
HEAD: 20631
SQRT: 20533
SQRTD: 20592
So using sqrtd the cost is 0.18%. I think that's acceptable.
I think so too.
I found one more issue with the 1.2--1.3 upgrade script, the DROP
FUNCTION pg_stat_statements(); should be DROP FUNCTION
pg_stat_statements(bool); since in 1.2 the function identity has changed.
I think all the outstanding issues are fixed in this patch.
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..a56f151
--- /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(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* 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..ad429ef 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,23 @@ 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);
+ /*
+ * Note we are calculating the population variance here, not the
+ * sample variance, as we have data for the whole population,
+ * so Bessel's correction is not used, and we don't divide by
+ * tmp.calls - 1.
+ */
+ if (tmp.calls > 1)
+ values[i++] =
+ Float8GetDatumFast(sqrtd(tmp.sum_var_time / tmp.calls));
+ 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 +1606,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 +2962,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..04b3f01 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>Population 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
