(2014/01/26 17:43), Mitsumasa KONDO wrote:
> 2014-01-26 Simon Riggs <si...@2ndquadrant.com <mailto:si...@2ndquadrant.com>>
>
> On 21 January 2014 19:48, Simon Riggs <si...@2ndquadrant.com
> <mailto:si...@2ndquadrant.com>> wrote:
> > On 21 January 2014 12:54, KONDO Mitsumasa
> <kondo.mitsum...@lab.ntt.co.jp
> <mailto:kondo.mitsum...@lab.ntt.co.jp>> wrote:
> >> Rebased patch is attached.
> >
> > Does this fix the Windows bug reported by Kumar on 20/11/2013 ?
Sorry, I was misunderstanding. First name of Mr. Rajeev Rastogi is Kumar! I
searched
only e-mail address and title by his name...
I don't have windows compiler enviroment, but attached patch might be fixed.
Could I ask Mr. Rajeev Rastogi to test my patch again?
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,84 **** static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
#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 JUMBLE_SIZE 1024 /* query serialization buffer size */
/*
--- 78,85 ----
#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 */
/*
***************
*** 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);
***************
*** 276,282 **** 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
--- 283,289 ----
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
***************
*** 964,969 **** pgss_store(const char *query, uint32 queryId,
--- 971,979 ----
const BufferUsage *bufusage,
pgssJumbleState *jstate)
{
+ double total_sqtime = total_time * total_time;
+ double min_time;
+ double max_time;
pgssHashKey key;
pgssEntry *entry;
char *norm_query = NULL;
***************
*** 1040,1051 **** pgss_store(const char *query, uint32 queryId,
--- 1050,1074 ----
SpinLockAcquire(&e->mutex);
+ /* calculate min and max time */
+ if (e->counters.min_time > total_time)
+ min_time = total_time;
+ else
+ min_time = e->counters.min_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;
***************
*** 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.
--- 1108,1130 ----
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.
***************
*** 1161,1166 **** pg_stat_statements(PG_FUNCTION_ARGS)
--- 1198,1206 ----
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));
***************
*** 1207,1212 **** pg_stat_statements(PG_FUNCTION_ARGS)
--- 1247,1268 ----
values[i++] = Int64GetDatumFast(tmp.calls);
values[i++] = Float8GetDatumFast(tmp.total_time);
+ if (detected_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);
***************
*** 1293,1298 **** entry_alloc(pgssHashKey *key, const char *query, int query_len, bool sticky)
--- 1349,1357 ----
{
/* 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 */
***************
*** 1397,1402 **** entry_reset(void)
--- 1456,1482 ----
}
/*
+ * 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
* the current jumble.
*/
***************
*** 2176,2178 **** comp_location(const void *a, const void *b)
--- 2256,2274 ----
else
return 0;
}
+ /*
+ * fast sqrt algorithm: 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,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
