correspondent is found.
</P>
</DIV>
+
+<DIV CLASS="SECT2">
+<H2 CLASS="SECT2">2. The <TT CLASS="STRUCTNAME">pg_store_plans</TT>
+View</H2>
+
+<P>The statistics of <TT CLASS="STRUCTNAME">pg_store_plans</TT> module itself are tracked and made available via a view named <TT CLASS="STRUCTNAME">pg_store_plans_info</TT>. This view contains only a single row. The columns of the view are shown in <A HREF="#PGSTOREPLANSINFO-COLUMNS">Table 2</A>.
+</P>
+
+<DIV CLASS="TABLE">
+<A NAME="PGSTOREPLANSINFO-COLUMNS"></A>
+<P><B>Table 2. <TT>pg_store_plans_info</TT> Columns</B>
+</P>
+<TABLE BORDER="1" CLASS="CALSTABLE" >
+<COL><COL><COL><COL>
+<THEAD><TR><TH>Name</TH><TH>Type</TH><TH>References</TH>
+ <TH>Description</TH></TR>
+</THEAD>
+<TBODY>
+<TR><TD><TT CLASS="STRUCTFIELD">dealloc</TT></TD>
+ <TD><TT CLASS="TYPE">bigint</TT></TD>
+ <TD></TD>
+ <TD>Total number of times pg_store_plans entries about the least-executed statements were deallocated because more distinct statements than pg_store_plans.max were observed.</TD></TR>
+<TR><TD><TT CLASS="STRUCTFIELD">stats_reset</TT></TD>
+ <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
+ <TD></TD>
+ <TD>Time at which all statistics in the pg_store_plans view were last reset.</TD></TR>
+</TBODY>
+</TABLE>
+</DIV>
+
+
<DIV CLASS="SECT2">
<H2 CLASS="SECT2">
-<A NAME="Functions">2. Functions</A>
+<A NAME="Functions">3. Functions</A>
</H2>
<DIV CLASS="VARIABLELIST">
<DL> <DT> <CODE CLASS="FUNCTION">pg_store_plans_reset() returns void</CODE>
named <CODE CLASS="FUNCTION">pg_store_plans</CODE >.
</P>
</DD>
+<DT> <CODE CLASS="FUNCTION">pg_store_plans_info() returns record</CODE>
+</DT>
+<DD>
+<P>
+ <CODE CLASS="FUNCTION">pg_store_plans_info</CODE> view is defined in terms of a function also named <CODE CLASS="FUNCTAION">pg_store_plans_info</CODE>.
+ </P>
+</DD>
<DT>
<CODE CLASS="FUNCTION">pg_store_hash_query(query text) returns oid</CODE>
</DT>
</DIV>
<DIV CLASS="SECT2">
<H2 CLASS="SECT2">
-<ANAME="Config">3. Configuration Parameters</A>
+<ANAME="Config">4. Configuration Parameters</A>
</H2>
<P>
</P>
to <TT CLASS="LITERAL">file</TT>, the plan texts are stored in a
temporary file as pg_stat_statements
does. <TT CLASS="LITERAL">shmem</TT> means to store plan texts
- on-memory. The default value is "file". See the discussion below
- for details.
+ on-memory. The default value is "file". See
+ the <A HREF="#MEMORY_SETTING">discussion below</A> for details.
</P>
</DD>
<DT>
</DL>
</DIV>
-<H2 CLASS="SECT3">
-<A NAME="memory setting">Discussion on plan_storage setting</A>
+<H2 CLASS="SECT2">
+<A NAME="MEMORY_SETTING">5. Discussion on plan_storage setting</A>
</H2>
-<P><TT CLASS="LITERAL">pg_store_plans</TT> claims additional shared memory proportional to <TT CLASS="VARNAME">pg_store_plans.max</TT>. When <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> is set to "shmem", it claims further additional shared memory to store plan texts in an amount of the product of the maximum number of plans to store (pg_store_plans.max) and the maximum length of individual plan (pg_store_plans.max_plan_length). If <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> is set to "file", plan texts are written to a temporary file as <TT CLASS="LITERAL">pg_stat_statements</TT> does. If <TT CLASS="VARNAME">pg_store_plans.max</TT> is not large enough to store all plans, <TT CLASS="LITERAL">pg_store_plans</TT> reclaims the space for new plans by evicting some portion of the entries. After several rounds of that eviction, <TT CLASS="LITERAL">pg_store_plans</TT> runs garbage collection on the temporary file, which might be painful for certain workloads.</P>
+<P><TT CLASS="LITERAL">pg_store_plans</TT> claims additional shared memory proportional to <TT CLASS="VARNAME">pg_store_plans.max</TT>. When <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> is set to "shmem", it claims further additional shared memory to store plan texts in an amount of the product of the maximum number of plans to store (pg_store_plans.max) and the maximum length of individual plan (pg_store_plans.max_plan_length). If <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> is set to "file", plan texts are written to a temporary file as <TT CLASS="LITERAL">pg_stat_statements</TT> does. If <TT CLASS="VARNAME">pg_store_plans.max</TT> is not large enough to store all plans, <TT CLASS="LITERAL">pg_store_plans</TT> reclaims the space for new plans by evicting some portion of the entries. After several rounds of that eviction, <TT CLASS="LITERAL">pg_store_plans</TT> runs garbage collection on the temporary file, which might be painful for certain workloads. You can see how frequntly that eviction happens in <TT CLASS="STRUCTNAME">pg_store_plans_info.dealloc</TT>.</P>
<P>If pg_store_plans.max is sufficiently large so that garbage collection doesn't happen, "file" is recommended as <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT>.
</P>
<P> These parameters must be set in
</DIV>
<DIV CLASS="SECT2">
<H2 CLASS="SECT2">
-<A NAME="Sample">4. Sample Output</A>
+<A NAME="Sample">6. Sample Output</A>
</H2>
<PRE CLASS="SCREEN">(postgresql.conf has following settings)
shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_store_plans" to load this file. \quit
+--- Define pg_store_plans_info
+CREATE FUNCTION pg_store_plans_info(
+ OUT dealloc bigint,
+ OUT stats_reset timestamp with time zone
+)
+RETURNS record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_store_plans_info AS
+ SELECT * FROM pg_store_plans_info();
+
+GRANT SELECT ON pg_store_plans_info TO PUBLIC;
+
-- Register functions.
CREATE FUNCTION pg_store_plans_reset()
RETURNS void
AS 'MODULE_PATHNAME'
-LANGUAGE C;
+LANGUAGE C PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_shorten(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_normalize(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_jsonplan(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_textplan(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_yamlplan(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_xmlplan(text)
RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans_hash_query(text)
RETURNS oid
AS 'MODULE_PATHNAME'
LANGUAGE C
-RETURNS NULL ON NULL INPUT;
+RETURNS NULL ON NULL INPUT PARALLEL SAFE;
CREATE FUNCTION pg_store_plans(
OUT userid oid,
OUT dbid oid,
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_store_plans_1_6'
-LANGUAGE C;
+LANGUAGE C
+VOLATILE PARALLEL SAFE;
-- Register a view on the function for ease of use.
CREATE VIEW pg_store_plans AS
} Counters;
/*
+ * Global statistics for pg_store_plans
+ */
+typedef struct pgspGlobalStats
+{
+ int64 dealloc; /* # of times entries were deallocated */
+ TimestampTz stats_reset; /* timestamp with all stats reset */
+} pgspGlobalStats;
+
+/*
* Statistics per plan
*
* NB: see the file read/write code before changing field order here.
Size extent; /* current extent of plan file */
int n_writers; /* number of active writers to query file */
int gc_count; /* plan file garbage collection cycle count */
+ pgspGlobalStats stats; /* global statistics for pgsp */
} pgspSharedState;
/*---- Local variables ----*/
Datum pg_store_plans_yamlplan(PG_FUNCTION_ARGS);
Datum pg_store_plans_xmlplan(PG_FUNCTION_ARGS);
Datum pg_store_plans_textplan(PG_FUNCTION_ARGS);
+Datum pg_store_plans_info(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_store_plans_reset);
PG_FUNCTION_INFO_V1(pg_store_plans_hash_query);
PG_FUNCTION_INFO_V1(pg_store_plans_shorten);
PG_FUNCTION_INFO_V1(pg_store_plans_normalize);
PG_FUNCTION_INFO_V1(pg_store_plans_jsonplan);
-PG_FUNCTION_INFO_V1(pg_store_plans_textplan);
PG_FUNCTION_INFO_V1(pg_store_plans_yamlplan);
PG_FUNCTION_INFO_V1(pg_store_plans_xmlplan);
+PG_FUNCTION_INFO_V1(pg_store_plans_textplan);
+PG_FUNCTION_INFO_V1(pg_store_plans_info);
#if PG_VERSION_NUM < 130000
#define COMPTAG_TYPE char
shared_state->extent = 0;
shared_state->n_writers = 0;
shared_state->gc_count = 0;
+ shared_state->stats.dealloc = 0;
+ shared_state->stats.stats_reset = GetCurrentTimestamp();
}
/* Be sure everyone agrees on the hash table entry size */
tuplestore_donestoring(tupstore);
}
+/* Number of output arguments (columns) for pg_stat_statements_info */
+#define PG_STORE_PLANS_INFO_COLS 2
+
+/*
+ * Return statistics of pg_stat_statements.
+ */
+Datum
+pg_store_plans_info(PG_FUNCTION_ARGS)
+{
+ pgspGlobalStats stats;
+ TupleDesc tupdesc;
+ Datum values[PG_STORE_PLANS_INFO_COLS];
+ bool nulls[PG_STORE_PLANS_INFO_COLS];
+
+ if (!shared_state || !hash_table)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_store_plans must be loaded via shared_preload_libraries")));
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ /* Read global statistics for pg_stat_statements */
+ {
+ volatile pgspSharedState *s = (volatile pgspSharedState *) shared_state;
+
+ SpinLockAcquire(&s->mutex);
+ stats = s->stats;
+ SpinLockRelease(&s->mutex);
+ }
+
+ values[0] = Int64GetDatum(stats.dealloc);
+ values[1] = TimestampTzGetDatum(stats.stats_reset);
+
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
/*
* Estimate shared memory space needed.
*/
}
pfree(entries);
+
+ /* Increment the number of times entries are deallocated */
+ {
+ volatile pgspSharedState *s = (volatile pgspSharedState *) shared_state;
+
+ SpinLockAcquire(&s->mutex);
+ s->stats.dealloc += 1;
+ SpinLockRelease(&s->mutex);
+ }
}
/*
}
/*
+ * Reset global statistics for pg_store_plans.
+ */
+ {
+ volatile pgspSharedState *s = (volatile pgspSharedState *) shared_state;
+ TimestampTz stats_reset = GetCurrentTimestamp();
+
+ SpinLockAcquire(&s->mutex);
+ s->stats.dealloc = 0;
+ s->stats.stats_reset = stats_reset;
+ SpinLockRelease(&s->mutex);
+ }
+
+ /*
* Write new empty plan file, perhaps even creating a new one to recover
* if the file was missing.
*/
OSDN Git Service
