I just realized I made a whitespace error when I modified the comments.
I hope I don't make a habit of sending erroneous mails.
Please accept my apologies, as well as a guaranteed whitespace-error-free patch
(updated to version 5 for clarity).
Julian
Julian Markwort schrieb am 2018-03-20:
> To anyone who followed along with this for so long, I'd like to present my
> newest version of this patch.
> As suggested by Arthur Zakirov, there is now only a single GUC (
> pg_stat_statements.plan_track ) responsible for the selection of the plans
> that should be tracked. Possible values are: all, none, good, or bad.
> I've mostly copied functionality from pl_handler.c . This resulted in the
> need to include varlena.h so I could use the SplitIdentifierString() function
> to parse the values, of which several (e.g.
> pg_stat_statements.plan_track='good, bad') could be used.
> I've also added a new GUC:
> pg_stat_statements.plan_fence_factor
> This GUC can be used to scale the fences of the interval, outside of which a
> plan might be updated.
> Right now, it is set to 1.5 (common factor for the definition of outliers in
> boxplots) and you can see through additional colums in the pg_stat_statements
> view, how often these fences are surpassed by execution times and how often
> the plans are updated. (The colums are: good_plan_outliers,
> good_plan_updates, bad_plan_outliers, bad_plan_updates and are primarily here
> for testing and review purposes and are not essential to this patch, they
> probably don't add any value for the average user)
> Similarly to the first suggestion by Arthur, I've also changed the plan_reset
> functionality - there is now only one function,
> pg_stat_statements_plan_reset(queryid bigint), overloaded with (queryid
> bigint, plantype cstring) args, that can be used to remove both plans (when
> omitting the cstring) or either of them. The cstring argument accepts 'good'
> or 'bad'.
> I also added more comments to the estimations of the quartiles and the
> calculation of the fences.
> The performance impact lies now at 139312 vs 141841 tps, so roughly 1.78%
> slower than default pg_stat_statements.
> The fact that these results are a little worse than the previous iteration is
> due to some changes in the definition of the fences which mistakenly
> calculated by adding the scaled interquartile distance to the mean, instead
> of adding it to the respective quartiles, which means that plan updates are
> triggered a little more often.
> For 4259631 transactions however, only 11 updates for the bad plans where
> triggered.
> I'm looking forward to your opinions!
> Julian
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 39b368b70e..49bb462d10 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,7 +4,8 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o $(WIN32RES)
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
+DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
pg_stat_statements--1.3--1.4.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
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 5318c3550c..2ca549686f 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -395,4 +395,40 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset() | 1 | 1
(8 rows)
+-- test to see if any plans have been recorded.
+SELECT
+ CASE WHEN good_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+ case | case | case | case
+------+------+------+------
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 1 | 1 | 1 | 1
+ 1 | 1 | 1 | 1
+ 1 | 1 | 1 | 1
+(9 rows)
+
+-- test if there is some text in the recorded plans.
+SELECT substr(good_plan, 0, 11), substr(bad_plan, 0, 11) FROM pg_stat_statements ORDER BY query COLLATE "C";
+ substr | substr
+------------+------------
+ |
+ |
+ |
+ |
+ |
+ |
+ Query Text | Query Text
+ Query Text | Query Text
+ Query Text | Query Text
+ Query Text | Query Text
+(10 rows)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
new file mode 100644
index 0000000000..6c8f743ee5
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
@@ -0,0 +1,82 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.6'" 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);
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements_reset();
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+DROP FUNCTION pg_stat_statements_reset();
+
+-- Register functions.
+CREATE FUNCTION pg_stat_statements_reset()
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements_plan_reset(IN queryid bigint)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements_plan_reset(IN queryid bigint, IN plantype cstring)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT query text,
+ OUT good_plan text,
+ OUT bad_plan 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 good_plan_time float8,
+ OUT good_plan_outliers int8,
+ OUT good_plan_updates int8,
+ OUT good_plan_timestamp timestamp,
+ OUT bad_plan_time float8,
+ OUT bad_plan_outliers int8,
+ OUT bad_plan_updates int8,
+ OUT bad_plan_timestamp timestamp,
+ 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_6'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+-- 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;
+REVOKE ALL ON FUNCTION pg_stat_statements_plan_reset(bigint) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_stat_statements_plan_reset(bigint, cstring) FROM PUBLIC;
+
+GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pg_read_all_stats;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 928673498a..c6f5e97b5c 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -63,6 +63,7 @@
#include "access/hash.h"
#include "catalog/pg_authid.h"
+#include "commands/explain.h"
#include "executor/instrument.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
@@ -78,6 +79,8 @@
#include "tcop/utility.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/timestamp.h"
+#include "utils/varlena.h"
PG_MODULE_MAGIC;
@@ -119,7 +122,8 @@ typedef enum pgssVersion
PGSS_V1_0 = 0,
PGSS_V1_1,
PGSS_V1_2,
- PGSS_V1_3
+ PGSS_V1_3,
+ PGSS_V1_6
} pgssVersion;
/*
@@ -165,6 +169,16 @@ typedef struct Counters
double usage; /* usage factor */
} Counters;
+typedef struct pgssPlan
+{
+ Size offset;
+ int len;
+ double time; /* execution time in msec when the latest plan was updated */
+ TimestampTz timestamp;
+ int64 outliers;
+ int64 updates;
+} pgssPlan;
+
/*
* Statistics per statement
*
@@ -178,6 +192,8 @@ typedef struct pgssEntry
Counters counters; /* the statistics for this query */
Size query_offset; /* query text offset in external file */
int query_len; /* # of valid bytes in query string, or -1 */
+ pgssPlan good_plan;
+ pgssPlan bad_plan;
int encoding; /* query text encoding */
slock_t mutex; /* protects the counters only */
} pgssEntry;
@@ -265,11 +281,26 @@ static const struct config_enum_entry track_options[] =
{NULL, 0, false}
};
+static const struct config_enum_entry format_options[] =
+{
+ {"text", EXPLAIN_FORMAT_TEXT, false},
+ {"xml", EXPLAIN_FORMAT_XML, false},
+ {"json", EXPLAIN_FORMAT_JSON, false},
+ {"yaml", EXPLAIN_FORMAT_YAML, false},
+ {NULL, 0, false}
+};
+
static int pgss_max; /* max # statements to track */
static int pgss_track; /* tracking level */
static bool pgss_track_utility; /* whether to track utility commands */
static bool pgss_save; /* whether to save stats across shutdown */
-
+static bool pgss_good_plan_enable; /* whether to save good_plans */
+static bool pgss_bad_plan_enable; /* whether to save bad_plans */
+static bool pgss_good_plan_enable_temp; /* used to store state between plan_track_check and _assign */
+static bool pgss_bad_plan_enable_temp; /* used to store state between plan_track_check and _assign */
+static int pgss_plan_format; /* id which sets the output format */
+static double pgss_plan_fence_factor; /* can be used to scale the fences for plan updates */
+static char *pgss_plan_track_str; /* String holding the arguments provided to the pgss_plan_track GUC */
#define pgss_enabled() \
(pgss_track == PGSS_TRACK_ALL || \
@@ -291,7 +322,9 @@ 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_1_6);
PG_FUNCTION_INFO_V1(pg_stat_statements);
+PG_FUNCTION_INFO_V1(pg_stat_statements_plan_reset);
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -309,12 +342,17 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
static uint64 pgss_hash_string(const char *str, int len);
static void pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
+ QueryDesc *queryDesc,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate);
+Datum pg_stat_statements_plan_reset(PG_FUNCTION_ARGS);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
bool showtext);
+static int fill_plan_times(Datum values[], bool nulls[], int i, pgssPlan *plan);
+static int fill_plan_str(Datum values[], bool nulls[], int i, pgssPlan *plan,
+ pgssEntry *entry, char *qbuffer, Size qbuffer_size);
static Size pgss_memsize(void);
static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
int encoding, bool sticky);
@@ -338,7 +376,8 @@ static char *generate_normalized_query(pgssJumbleState *jstate, const char *quer
static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query,
int query_loc);
static int comp_location(const void *a, const void *b);
-
+static bool plan_track_check_hook(char **newvalue, void **extra, GucSource source);
+static void plan_track_assign_hook(const char *newvalue, void *extra);
/*
* Module load callback
@@ -407,6 +446,42 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomStringVariable("pg_stat_statements.plan_track",
+ "Enable plan tracking",
+ "Specify the types of plans that shall be tracked: all, none, good, or bad. Support for multiple individual types (curently only good and bad) is available.",
+ &pgss_plan_track_str,
+ "all",
+ PGC_SUSET,
+ GUC_LIST_INPUT,
+ plan_track_check_hook,
+ plan_track_assign_hook,
+ NULL);
+
+ DefineCustomEnumVariable("pg_stat_statements.plan_format",
+ "Sets the output format for the plans.",
+ "Notice that the plan format can not be changed after a plan is saved. Valid values are text, json, xml and yaml.",
+ &pgss_plan_format,
+ EXPLAIN_FORMAT_TEXT,
+ format_options,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomRealVariable("pg_stat_statements.plan_fence_factor",
+ "Sets the factor that scales the fences that are used to determine whether a plan should be updated.",
+ NULL,
+ &pgss_plan_fence_factor,
+ 1.5,
+ 0.0,
+ +INFINITY,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
EmitWarningsOnPlaceholders("pg_stat_statements");
/*
@@ -470,7 +545,11 @@ pgss_shmem_startup(void)
int32 pgver;
int32 i;
int buffer_size;
+ int good_plan_buffer_size;
+ int bad_plan_buffer_size;
char *buffer = NULL;
+ char *good_plan_buffer = NULL;
+ char *bad_plan_buffer = NULL;
if (prev_shmem_startup_hook)
prev_shmem_startup_hook();
@@ -561,7 +640,11 @@ pgss_shmem_startup(void)
}
buffer_size = 2048;
+ good_plan_buffer_size = 2048;
+ bad_plan_buffer_size = 2048;
buffer = (char *) palloc(buffer_size);
+ good_plan_buffer = (char *) palloc(good_plan_buffer_size);
+ bad_plan_buffer = (char *) palloc(bad_plan_buffer_size);
if (fread(&header, sizeof(uint32), 1, file) != 1 ||
fread(&pgver, sizeof(uint32), 1, file) != 1 ||
@@ -595,8 +678,34 @@ pgss_shmem_startup(void)
if (fread(buffer, 1, temp.query_len + 1, file) != temp.query_len + 1)
goto read_error;
+ /* Resize good plan_buffer and read into it */
+ if (temp.good_plan.len >= good_plan_buffer_size)
+ {
+ good_plan_buffer_size =
+ Max(good_plan_buffer_size * 2, temp.good_plan.len + 1);
+ good_plan_buffer= repalloc(good_plan_buffer, good_plan_buffer_size);
+ }
+
+ if (fread(good_plan_buffer, 1, temp.good_plan.len + 1, file)
+ != temp.good_plan.len + 1)
+ goto read_error;
+
+ /* Resize bad plan_buffer and read into it */
+ if (temp.bad_plan.len >= bad_plan_buffer_size)
+ {
+ bad_plan_buffer_size =
+ Max(bad_plan_buffer_size * 2, temp.bad_plan.len + 1);
+ bad_plan_buffer= repalloc(bad_plan_buffer, bad_plan_buffer_size);
+ }
+
+ if (fread(bad_plan_buffer, 1, temp.bad_plan.len + 1, file)
+ != temp.bad_plan.len + 1)
+ goto read_error;
+
/* Should have a trailing null, but let's make sure */
buffer[temp.query_len] = '\0';
+ good_plan_buffer[temp.good_plan.len] = '\0';
+ bad_plan_buffer[temp.bad_plan.len] = '\0';
/* Skip loading "sticky" entries */
if (temp.counters.calls == 0)
@@ -608,6 +717,20 @@ pgss_shmem_startup(void)
goto write_error;
pgss->extent += temp.query_len + 1;
+ /* Store the good plan text*/
+ temp.good_plan.offset = pgss->extent;
+ if (fwrite(good_plan_buffer, 1, temp.good_plan.len + 1, qfile)
+ != temp.good_plan.len + 1)
+ goto write_error;
+ pgss->extent += temp.good_plan.len + 1;
+
+ /* Store the bad plan text*/
+ temp.bad_plan.offset = pgss->extent;
+ if (fwrite(bad_plan_buffer, 1, temp.bad_plan.len + 1, qfile)
+ != temp.bad_plan.len + 1)
+ goto write_error;
+ pgss->extent += temp.bad_plan.len + 1;
+
/* make the hashtable entry (discards old entries if too many) */
entry = entry_alloc(&temp.key, query_offset, temp.query_len,
temp.encoding,
@@ -615,9 +738,14 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
+ /* copy in the plans */
+ entry->good_plan = temp.good_plan;
+ entry->bad_plan = temp.bad_plan;
}
pfree(buffer);
+ pfree(good_plan_buffer);
+ pfree(bad_plan_buffer);
FreeFile(file);
FreeFile(qfile);
@@ -658,6 +786,10 @@ write_error:
fail:
if (buffer)
pfree(buffer);
+ if (good_plan_buffer)
+ pfree(good_plan_buffer);
+ if (bad_plan_buffer)
+ pfree(bad_plan_buffer);
if (file)
FreeFile(file);
if (qfile)
@@ -723,14 +855,52 @@ pgss_shmem_shutdown(int code, Datum arg)
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
int len = entry->query_len;
+ int good_plan_len = entry->good_plan.len;
+ int bad_plan_len = entry->bad_plan.len;
char *qstr = qtext_fetch(entry->query_offset, len,
qbuffer, qbuffer_size);
+ char *good_plan_str;
+ char *bad_plan_str;
+ if (good_plan_len > 0)
+ {
+ /* A good plan is available */
+ good_plan_str = qtext_fetch(entry->good_plan.offset, good_plan_len,
+ qbuffer, qbuffer_size);
+ }
+ else
+ {
+ /*
+ * There is no good plan available. This can be caused by a query
+ * which has no plan (for example insert or delete queries) or
+ * because good plans are disabled. In this case we have to store
+ * an empty string instead of null.
+ */
+ good_plan_str = "";
+ }
+ if (bad_plan_len > 0)
+ {
+ /* A bad plan is available */
+ bad_plan_str = qtext_fetch(entry->bad_plan.offset, bad_plan_len,
+ qbuffer, qbuffer_size);
+ }
+ else
+ {
+ /*
+ * There is no bad plan available. This can be caused by a query
+ * which has no plan (for example insert or delete queries) or
+ * because bad plans are disabled. In this case we have to store
+ * an empty string instead of null.
+ */
+ bad_plan_str = "";
+ }
if (qstr == NULL)
continue; /* Ignore any entries with bogus texts */
-
+ /* Write entries, queries and plans serialized to the dump file */
if (fwrite(entry, sizeof(pgssEntry), 1, file) != 1 ||
- fwrite(qstr, 1, len + 1, file) != len + 1)
+ fwrite(qstr, 1, len + 1, file) != len + 1 ||
+ fwrite(good_plan_str, 1, good_plan_len + 1, file) != good_plan_len + 1 ||
+ fwrite(bad_plan_str, 1, bad_plan_len + 1, file) != bad_plan_len + 1)
{
/* note: we assume hash_seq_term won't change errno */
hash_seq_term(&hash_seq);
@@ -835,6 +1005,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
query->queryId,
query->stmt_location,
query->stmt_len,
+ NULL,
0,
0,
NULL,
@@ -942,6 +1113,7 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
queryId,
queryDesc->plannedstmt->stmt_location,
queryDesc->plannedstmt->stmt_len,
+ queryDesc, /* queryDesc itself is needed to retrieve the plan */
queryDesc->totaltime->total * 1000.0, /* convert to msec */
queryDesc->estate->es_processed,
&queryDesc->totaltime->bufusage,
@@ -1053,6 +1225,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
0, /* signal that it's a utility stmt */
pstmt->stmt_location,
pstmt->stmt_len,
+ NULL,
INSTR_TIME_GET_MILLISEC(duration),
rows,
&bufusage,
@@ -1083,6 +1256,26 @@ pgss_hash_string(const char *str, int len)
len, 0));
}
+/*
+ * Update a plan in the text file.
+ * We can re-use qtext_store to save the plan.
+ */
+static void
+update_plan(volatile pgssPlan* pgssPlan_ptr, const char* plan_str, int plan_len,
+ double total_time)
+{
+ if (plan_len > 0)
+ {
+ Size plan_offset;
+ qtext_store(plan_str, plan_len, &plan_offset, NULL);
+ pgssPlan_ptr->offset = plan_offset;
+ pgssPlan_ptr->len = plan_len;
+ pgssPlan_ptr->time = total_time;
+ pgssPlan_ptr->timestamp = GetCurrentTimestamp();
+ }
+ return;
+}
+
/*
* Store some statistics for a statement.
*
@@ -1096,6 +1289,7 @@ pgss_hash_string(const char *str, int len)
static void
pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
+ QueryDesc *queryDesc,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate)
@@ -1134,6 +1328,7 @@ pgss_store(const char *query, uint64 queryId,
query_len = strlen(query);
}
+
/*
* Discard leading and trailing whitespace, too. Use scanner_isspace()
* not libc's isspace(), because we want to match the lexer's behavior.
@@ -1231,9 +1426,136 @@ pgss_store(const char *query, uint64 queryId,
* locking rules at the head of the file)
*/
volatile pgssEntry *e = (volatile pgssEntry *) entry;
+ bool good_plan_needs_updating = false;
+ bool bad_plan_needs_updating = false;
+ int plan_len = 0;
+ char* plan;
SpinLockAcquire(&e->mutex);
+ if (queryDesc && (pgss_good_plan_enable || pgss_bad_plan_enable))
+ {
+ /* Avoid division by zero errors. */
+ if (e->counters.calls > 0)
+ {
+ /*
+ * The quantiles for a normal distributions can be estimated through
+ * X = expected value + standard deviation * Z-score
+ * For the 25% and 75% quantiles (=lower and upper quartile),
+ * the z-scores -0.6754 and 0.6745 are used.
+ */
+ double z_score = 0.6745;
+ /*
+ * Since we're already making a bunch of statistical assumptions,
+ * let's use the fact that the expected value can be approximated
+ * by the mean of a dataset (see "law of large numbers").
+ */
+
+ double std_dev = sqrt(e->counters.sum_var_time / e->counters.calls);
+ double exp_val = e->counters.mean_time;
+
+ double lower_quartile = exp_val - std_dev * z_score;
+ double upper_quartile = exp_val + std_dev * z_score;
+
+ double interquartile_dist = upper_quartile - lower_quartile;
+
+ /*
+ * We calculate a set of inner and outer fences, similar to the
+ * definition of whiskers in boxplots.
+ * By default, pgss_plan_fence_factor is set to 1.5 .
+ */
+ double lower_fence = lower_quartile - pgss_plan_fence_factor * interquartile_dist;
+ double upper_fence = upper_quartile + pgss_plan_fence_factor * interquartile_dist;
+
+ if (pgss_good_plan_enable)
+ {
+ /*
+ * Only update bad_plan if execution time was slower than the lower fence.
+ * The use of statistics to estimate wether the change in execution time
+ * was big enough to warrant a plan update is debatable, however it is
+ * probably the fastest way to identify changes in the planner
+ * without actually comparing the plans step by step.
+ */
+ if (total_time < lower_fence)
+ {
+ e->good_plan.outliers++;
+ /* Only update plan if execution time was slower than that of the old plan*/
+ if (total_time < e->good_plan.time)
+ {
+ good_plan_needs_updating = true;
+ e->good_plan.updates++;
+ }
+ }
+ }
+
+ if (pgss_bad_plan_enable)
+ {
+ /* Only update good_plan if execution time was faster than the upperer fence.*/
+ if (total_time > upper_fence)
+ {
+ e->bad_plan.outliers++;
+ if (total_time > e->bad_plan.time)
+ {
+ bad_plan_needs_updating = true;
+ e->bad_plan.updates++;
+ }
+ }
+ }
+ }
+
+ /*
+ * Plans should be updated whenever no previous plan was stored.
+ */
+ if (pgss_good_plan_enable && e->good_plan.time==0)
+ good_plan_needs_updating = true;
+ if (pgss_bad_plan_enable && e->bad_plan.time==0)
+ bad_plan_needs_updating = true;
+
+ if (good_plan_needs_updating || bad_plan_needs_updating)
+ {
+ /* *es is used to retrieve the plan associated with the statement*/
+ ExplainState *es = NewExplainState();
+
+ es->analyze = (queryDesc->instrument_options && false);
+ es->verbose = false;
+ es->buffers = (es->analyze && false);
+ es->timing = (es->analyze && true);
+ es->format = pgss_plan_format;
+
+ ExplainBeginOutput(es);
+ ExplainQueryText(es, queryDesc);
+ ExplainPrintPlan(es, queryDesc);
+ ExplainEndOutput(es);
+
+ /* Remove last line break */
+ if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
+ es->str->data[--es->str->len] = '\0';
+
+ /* Fix JSON to output an object */
+ if (pgss_plan_format == EXPLAIN_FORMAT_JSON)
+ {
+ es->str->data[0] = '{';
+ es->str->data[es->str->len - 1] = '}';
+ }
+
+ plan = es->str->data;
+ plan_len = strlen(plan);
+
+ if (good_plan_needs_updating)
+ {
+ update_plan(&e->good_plan, plan, plan_len, total_time);
+ }
+
+ if (bad_plan_needs_updating)
+ {
+ update_plan(&e->bad_plan, plan, plan_len, total_time);
+ }
+
+ pfree(es->str->data);
+ pfree(es);
+ }
+ }
+
/* "Unstick" entry if it was previously sticky */
if (e->counters.calls == 0)
e->counters.usage = USAGE_INIT;
@@ -1305,12 +1627,76 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum
+pg_stat_statements_plan_reset(PG_FUNCTION_ARGS)
+{
+ pgssEntry *entry;
+ pgssHashKey key;
+
+ uint64 query_id = PG_GETARG_INT64(0);
+
+ bool good_plan_reset = false;
+ bool bad_plan_reset = false;
+
+ if (PG_NARGS() == 2)
+ {
+ char *plan_type_str = PG_GETARG_CSTRING(1);
+ if (pg_strcasecmp(plan_type_str, "good") == 0)
+ good_plan_reset = true;
+ else if (pg_strcasecmp(plan_type_str, "bad") == 0)
+ bad_plan_reset = true;
+ else
+ elog(LOG, "If a second argument is provided to pg_stat_statements_plan_reset, it must be of a valid plan type ('good' or 'bad').");
+ }
+ else
+ {
+ good_plan_reset = true;
+ bad_plan_reset = true;
+ }
+
+ if (!pgss || !pgss_hash)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
+
+ /* Set up key for hashtable search */
+ key.userid = GetUserId();
+ key.dbid = MyDatabaseId;
+ key.queryid = query_id;
+
+ /* Lookup the hash table entry with shared lock. */
+ LWLockAcquire(pgss->lock, LW_SHARED);
+
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+
+ if (entry)
+ {
+ if (good_plan_reset)
+ {
+ entry->good_plan.offset = -1;
+ entry->good_plan.len = 0;
+ entry->good_plan.time = 0;
+ entry->good_plan.timestamp = 0;
+ }
+ if (bad_plan_reset)
+ {
+ entry->bad_plan.offset = -1;
+ entry->bad_plan.len = 0;
+ entry->bad_plan.time = 0;
+ entry->bad_plan.timestamp = 0;
+ }
+ }
+ LWLockRelease(pgss->lock);
+ 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_V1_3 23
-#define PG_STAT_STATEMENTS_COLS 23 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_6 33
+#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1322,6 +1708,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_6(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_6, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
@@ -1428,6 +1824,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_3)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_6:
+ if (api_version != PGSS_V1_6)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1511,6 +1911,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
bool nulls[PG_STAT_STATEMENTS_COLS];
int i = 0;
Counters tmp;
+ pgssPlan tmp_good_plan;
+ pgssPlan tmp_bad_plan;
double stddev;
int64 queryid = entry->key.queryid;
@@ -1550,6 +1952,18 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
/* Just return a null if we fail to find the text */
nulls[i++] = true;
}
+
+ if (api_version >= PGSS_V1_6)
+ {
+ /*
+ * This version requires to print out the good
+ * and the bad plan if they are enabled.
+ */
+ i = fill_plan_str(values, nulls, i, &entry->good_plan,
+ entry, qbuffer, qbuffer_size);
+ i = fill_plan_str(values, nulls, i, &entry->bad_plan,
+ entry, qbuffer, qbuffer_size);
+ }
}
else
{
@@ -1579,6 +1993,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
SpinLockAcquire(&e->mutex);
tmp = e->counters;
+ tmp_good_plan = e->good_plan;
+ tmp_bad_plan = e->bad_plan;
SpinLockRelease(&e->mutex);
}
@@ -1606,6 +2022,14 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
stddev = 0.0;
values[i++] = Float8GetDatumFast(stddev);
}
+
+ if (api_version >= PGSS_V1_6)
+ {
+ /* Set plan times and timestamps */
+ i = fill_plan_times(values, nulls, i, &tmp_good_plan);
+ i = fill_plan_times(values, nulls, i, &tmp_bad_plan);
+ }
+
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
@@ -1629,6 +2053,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
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 :
+ api_version == PGSS_V1_6 ? PG_STAT_STATEMENTS_COLS_V1_6 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -1643,6 +2068,54 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
tuplestore_donestoring(tupstore);
}
+/* Fill the plan time and timestamp into the values array. */
+static int
+fill_plan_times(Datum values[], bool nulls[], int i, pgssPlan *plan)
+{
+ values[i++] = Float8GetDatumFast(plan->time);
+ values[i++] = Int64GetDatumFast(plan->outliers);
+ values[i++] = Int64GetDatumFast(plan->updates);
+
+ /* if there is noting in the timestamp field, we are not interested in it */
+ if (plan->timestamp)
+ values[i++] = TimestampTzGetDatum(plan->timestamp);
+ else
+ nulls[i++] = true;
+ return i;
+}
+
+/* Fill the plan string into the values array. */
+static int
+fill_plan_str(Datum values[], bool nulls[], int i, pgssPlan *plan,
+ pgssEntry *entry, char *qbuffer, Size qbuffer_size)
+{
+ if (plan && plan->len > 0)
+ {
+ char *pstr = qtext_fetch(plan->offset, plan->len, qbuffer, qbuffer_size);
+ if (pstr)
+ {
+ char *enc;
+ enc = pg_any_to_server(pstr, plan->len, entry->encoding);
+ values[i++] = CStringGetTextDatum(enc);
+
+ if (enc != pstr)
+ pfree(enc);
+ }
+ else
+ {
+ /* failed to get the string of the plan */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* no plan available or plan_len not greater than 0 */
+ nulls[i++] = true;
+ }
+ return i;
+}
+
+
/*
* Estimate shared memory space needed.
*/
@@ -1694,6 +2167,8 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
/* reset the statistics */
memset(&entry->counters, 0, sizeof(Counters));
+ memset(&entry->good_plan, 0, sizeof(pgssPlan));
+ memset(&entry->bad_plan, 0, sizeof(pgssPlan));
/* set the appropriate initial usage count */
entry->counters.usage = sticky ? pgss->cur_median_usage : USAGE_INIT;
/* re-initialize the mutex each time ... we assume no one using it */
@@ -2104,6 +2579,18 @@ gc_qtexts(void)
qbuffer,
qbuffer_size);
+ int good_plan_len = entry->good_plan.len;
+ char *good_plan = qtext_fetch(entry->good_plan.offset,
+ good_plan_len,
+ qbuffer,
+ qbuffer_size);
+
+ int bad_plan_len = entry->bad_plan.len;
+ char *bad_plan = qtext_fetch(entry->bad_plan.offset,
+ bad_plan_len,
+ qbuffer,
+ qbuffer_size);
+
if (qry == NULL)
{
/* Trouble ... drop the text */
@@ -2125,6 +2612,53 @@ gc_qtexts(void)
entry->query_offset = extent;
extent += query_len + 1;
+
+ if (good_plan == NULL || good_plan_len <= 0)
+ {
+ /* There was an error while loading the good_plan or there was simply never a
+ * good_plan recorded, so we make sure that this entry knows this. */
+ entry->good_plan.offset = 0;
+ entry->good_plan.len = -1;
+ }
+ else
+ {
+ /* Save the good plan */
+ if (fwrite(good_plan, 1, good_plan_len + 1, qfile) != good_plan_len + 1)
+ {
+ ereport(LOG,
+ (errcode_for_file_access(),
+ errmsg("could not write pg_stat_statement file \"%s\": %m",
+ PGSS_TEXT_FILE)));
+ hash_seq_term(&hash_seq);
+ goto gc_fail;
+ }
+ entry->good_plan.offset = extent;
+ extent += good_plan_len + 1;
+ }
+
+ if (bad_plan == NULL || bad_plan_len <= 0)
+ {
+ /* There was an error while loading the bad_plan or there was simply never a
+ * bad_plan recorded, so we make sure that this entry knows this. */
+ entry->bad_plan.offset = 0;
+ entry->bad_plan.len = -1;
+ }
+ else
+ {
+ /* Save the bad plan */
+ if (fwrite(bad_plan, 1, bad_plan_len + 1, qfile) != bad_plan_len + 1)
+ {
+ ereport(LOG,
+ (errcode_for_file_access(),
+ errmsg("could not write pg_stat_statement file \"%s\": %m",
+ PGSS_TEXT_FILE)));
+ hash_seq_term(&hash_seq);
+ goto gc_fail;
+ }
+ entry->bad_plan.offset = extent;
+ extent += bad_plan_len + 1;
+ }
+
nentries++;
}
@@ -3163,3 +3697,81 @@ comp_location(const void *a, const void *b)
else
return 0;
}
+
+/*
+ * Check the list of plans that shall be tracked and set the
+ * appropriate temporary bools.
+ */
+static bool
+plan_track_check_hook(char **newvalue, void **extra, GucSource source)
+{
+ char *rawstring;
+ List *elemlist;
+ ListCell *l;
+
+ /* Set all temp bools to false */
+ pgss_good_plan_enable_temp = false;
+ pgss_bad_plan_enable_temp = false;
+
+ if (pg_strcasecmp(*newvalue, "all") == 0)
+ {
+ pgss_good_plan_enable_temp = true;
+ pgss_bad_plan_enable_temp = true;
+ }
+ else if (pg_strcasecmp(*newvalue, "none") == 0)
+ return true;
+ else
+ {
+ /* Need a modifiable copy of string */
+ rawstring = pstrdup(*newvalue);
+
+ /* Parse string into list of identifiers */
+ if (!SplitIdentifierString(rawstring, ',', &elemlist))
+ {
+ /* syntax error in list */
+ GUC_check_errdetail("List syntax is invalid.");
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+
+ foreach(l, elemlist)
+ {
+ char *tok = (char *) lfirst(l);
+
+ if (pg_strcasecmp(tok, "good") == 0)
+ pgss_good_plan_enable_temp = true;
+ else if (pg_strcasecmp(tok, "bad") == 0)
+ pgss_bad_plan_enable_temp = true;
+ else if (pg_strcasecmp(tok, "all") == 0 || pg_strcasecmp(tok, "none") == 0)
+ {
+ GUC_check_errdetail("Key word \"%s\" cannot be combined with other key words.", tok);
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+ else
+ {
+ GUC_check_errdetail("Unrecognized key word: \"%s\".", tok);
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+ }
+ pfree(rawstring);
+ list_free(elemlist);
+ }
+
+ return true;
+}
+
+/*
+ * The bools enabling and disabling tracking of certain plans
+ * are set to the values previously set in the temporary variables.
+ */
+static void
+plan_track_assign_hook(const char *newvalue, void *extra)
+{
+ pgss_good_plan_enable = pgss_good_plan_enable_temp;
+ pgss_bad_plan_enable = pgss_bad_plan_enable_temp;
+}
\ No newline at end of file
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 193fcdfafa..617038b4c0 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.5'
+default_version = '1.6'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index a8361fd1bf..4eb49f84c6 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -195,4 +195,15 @@ DROP FUNCTION PLUS_TWO(INTEGER);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- test to see if any plans have been recorded.
+SELECT
+ CASE WHEN good_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- test if there is some text in the recorded plans.
+SELECT substr(good_plan, 0, 11), substr(bad_plan, 0, 11) FROM pg_stat_statements ORDER BY query COLLATE "C";
+
DROP EXTENSION pg_stat_statements;
