public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Optionally record Plan IDs to track plan changes for a query
2+ messages / 2 participants
[nested] [flat]
* [PATCH] Optionally record Plan IDs to track plan changes for a query
@ 2025-01-02 20:46 Lukas Fittl <[email protected]>
2025-01-24 09:23 ` Re: [PATCH] Optionally record Plan IDs to track plan changes for a query Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Lukas Fittl @ 2025-01-02 20:46 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Marko M <[email protected]>; Sami Imseih <[email protected]>
Hi all,
Inspired by a prior proposal by Sami Imseih for tracking Plan IDs [0], as
well as extensions like pg_stat_plans [1] (unmaintained), pg_store_plans
[2] (not usable on production, see notes later) and aurora_stat_plans [3]
(enabled by default on AWS), this proposed patch set adds:
1. An updated in-core facility to optionally track Plan IDs based on
hashing the plan nodes during the existing treewalk in setrefs.c -
controlled by the new "compute_plan_id" GUC
2. An example user of plan IDs with a new pg_stat_plans extension in
contrib, that also records the first plan text with EXPLAIN (COSTS OFF)
My overall perspective is that (1) is best done in-core to keep overhead
low, whilst (2) could be done outside of core (or merged with a future
pg_stat_statements) and is included here mainly for illustration purposes.
Notes including what constitutes a plan ID follow, after a quick example:
## Example
Having the planid + an extension that records it, plus the first plan text,
lets you track different plans for the same query:
bench=# SELECT * FROM pgbench_accounts WHERE aid = 123;
bench=# SET enable_indexscan = off;
bench=# SELECT * FROM pgbench_accounts WHERE aid = 123;
bench=# SELECT queryid, planid, plan FROM pg_stat_plans WHERE plan LIKE
'%pgbench%';
queryid | planid |
plan
----------------------+----------------------+------------------------------------------------------------
-5986989572677096226 | -2057350818695327558 | Index Scan using
pgbench_accounts_pkey on pgbench_accounts+
| | Index Cond: (aid = 123)
-5986989572677096226 | 2815444815385882663 | Bitmap Heap Scan on
pgbench_accounts +
| | Recheck Cond: (aid = 123)
+
| | -> Bitmap Index Scan on
pgbench_accounts_pkey +
| | Index Cond: (aid =
123)
And this also supports showing the plan for a currently running query (call
count is zero in such cases):
session 1:
bench# SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts;
session 2:
bench=# SELECT query, plan FROM pg_stat_activity
JOIN pg_stat_plans ON (usesysid = userid AND datid = dbid AND query_id =
queryid AND plan_id = planid)
WHERE query LIKE 'SELECT pg_sleep%';
query |
plan
-------------------------------------------------------+------------------------------------
SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts; | Aggregate
+
| -> Seq Scan on
pgbench_accounts
## What is a plan ID?
My overall hypothesis here is that identifying different plan shapes for
the same normalized query (i.e. queryid) is useful, because it lets you
detect use of different plan choices such as which join order or index was
used based on different input parameters (or different column statistics
due to a recent ANALYZE) for the same normalized query.
You can get this individually for a given query with EXPLAIN of course, but
if you want to track this over time the only workable mechanism in my
experience is auto_explain, which is good for sampling outliers, but bad
for getting a comprehensive view of which plans where used and how often.
To me the closest to what I consider a "plan shape" is the output of
EXPLAIN (COSTS OFF), that is, the plan nodes and their filters/conditions,
but discarding the exact costs as well as ignoring any execution
statistics. The idea behind the proposed plan ID implementation is trying
to match that by hashing plan nodes, similar to how query IDs hash
post-parse analysis query nodes.
One notable edge case are plans that involve partitions - those could of
course lead to a lot of different planids for a given queryid, based on how
many partitions were pruned. We could consider special casing this, e.g. by
trying to be smart about declarative partitioning, and considering plans to
be identical if they scan the same number of partitions with the same scan
methods. However this could also be done by an out-of-core extension,
either by defining a better planid mechanism, or maintaining a grouped
planid of sorts based on the internal planid.
The partitions problem reminds me a bit of the IN list problem with
pg_stat_statements (which we still haven't resolved) - despite the problem
the extension has been successfully used for many years by many Postgres
users, even for those workloads where you have thousands of entries for the
same query with different IN list lengths.
## Why does this need to be in core?
Unfortunately both existing open-source extensions I'm familiar with are
not suitable for production use. Out of the two, only pg_store_plans [2] is
being maintained, however it carries significant overhead because it
calculates the plan ID by hashing the EXPLAIN text output every time a
query is executed.
My colleague Marko (CCed) and I evaluated whether pg_store_plans could be
modified to instead calculate the planid by hashing the plan tree, and ran
into three issues:
1. The existing node jumbling in core is not usable by extensions, and it
is necessary to have something like it for hashing Filters/Conds
(ultimately requiring us to duplicate all of it in the extension, and keep
maintaining that for every major release)
2. Whilst its cheap enough, it seems unnecessary to do an additional tree
walk when setrefs.c already walks the plan tree in a near-final state
3. It seems useful to enable showing the plan shape of a currently running
query (e.g. to identify whether a plan regression causes the query to run
forever), and this is much easier to do by adding planid to
pg_stat_activity, like the queryid
I also suspect that Aurora's implementation in [3] had some in-core
modifications to enable it work efficiently, but I'm not familiar with any
implementation details beyond what's in the public documentation.
## Implementation notes
The attached patch set includes two preparatory patches that could be
committed independently if deemed useful:
The first patch allows use of node jumbling by other unit files /
extensions, which would help an out-of-core extension avoid duplicating all
the node jumbling code.
The second patch adds a function for the extensible cumulative statistics
system to drop all entries for a given statistics kind. This already exists
for resetting, but in case of a dynamic list of entries its more useful to
be able to drop all of them when "reset" is called.
The third patch adds plan ID tracking in core. This is turned off by
default, and can be enabled by setting "compute_plan_id" to "on". Plan IDs
are shown in pg_stat_activity, as well as EXPLAIN and auto_explain output,
to allow matching a given plan ID to a plan text, without requiring the use
of an extension. There are some minor TODOs in the plan jumbling logic that
I haven't finalized yet. There is also an open question whether we should
use the node attribute mechanism instead of custom jumbling logic?
The fourth patch adds the pg_stat_plans contrib extension, for illustrative
purposes. This is inspired by pg_stat_statements, but intentionally kept
separate for easier review and since it does not use an external file and
could technically be used independently. We may want to develop this into a
unified pg_stat_statements+plans in-core mechanism in the future, but I
think that is best kept for a separate discussion.
The pg_stat_plans extension utilizes the cumulative statistics system for
tracking statistics (extensible thanks to recent changes!), as well as
dynamic shared memory to track plan texts up to a given limit (2kB by
default). As a side note, managing extra allocations with the new
extensible stats is a bit cumbersome - it would be helpful to have a hook
for cleaning up data associated to entries (like a DSA allocation).
Thanks,
Lukas
[0]:
https://www.postgresql.org/message-id/flat/604E3199-2DD2-47DD-AC47-774A6F97DCA9%40amazon.com
[1]: https://github.com/2ndQuadrant/pg_stat_plans
[2]: https://ossc-db.github.io/pg_store_plans/
[3]:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora_stat_plans.html
--
Lukas Fittl
Attachments:
[application/octet-stream] 0002-Cumulative-statistics-Add-pgstat_drop_entries_of_kin.patch (2.5K, 3-0002-Cumulative-statistics-Add-pgstat_drop_entries_of_kin.patch)
download | inline diff:
From d61a9c94325f980c76d548be6db6f6e86f96e0cd Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Thu, 2 Jan 2025 10:46:30 -0800
Subject: [PATCH 2/4] Cumulative statistics: Add pgstat_drop_entries_of_kind
helper
This allows users of the cumulative statistics systems to drop all
entries for a given kind, similar to how pgstat_reset_entries_of_kind
allows resetting all entreis for a given statistics kind.
---
src/backend/utils/activity/pgstat_shmem.c | 33 +++++++++++++++++++++++
src/include/utils/pgstat_internal.h | 1 +
2 files changed, 34 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_shmem.c b/src/backend/utils/activity/pgstat_shmem.c
index bcc8b2eb4f..43f2dfd695 100644
--- a/src/backend/utils/activity/pgstat_shmem.c
+++ b/src/backend/utils/activity/pgstat_shmem.c
@@ -1015,6 +1015,39 @@ pgstat_drop_all_entries(void)
pgstat_request_entry_refs_gc();
}
+void
+pgstat_drop_entries_of_kind(PgStat_Kind kind)
+{
+ dshash_seq_status hstat;
+ PgStatShared_HashEntry *ps;
+ uint64 not_freed_count = 0;
+
+ dshash_seq_init(&hstat, pgStatLocal.shared_hash, true);
+ while ((ps = dshash_seq_next(&hstat)) != NULL)
+ {
+ if (ps->dropped || ps->key.kind != kind)
+ continue;
+
+ /* delete local reference */
+ if (pgStatEntryRefHash)
+ {
+ PgStat_EntryRefHashEntry *lohashent =
+ pgstat_entry_ref_hash_lookup(pgStatEntryRefHash, ps->key);
+
+ if (lohashent)
+ pgstat_release_entry_ref(lohashent->key, lohashent->entry_ref,
+ true);
+ }
+
+ if (!pgstat_drop_entry_internal(ps, &hstat))
+ not_freed_count++;
+ }
+ dshash_seq_term(&hstat);
+
+ if (not_freed_count > 0)
+ pgstat_request_entry_refs_gc();
+}
+
static void
shared_stat_reset_contents(PgStat_Kind kind, PgStatShared_Common *header,
TimestampTz ts)
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 811ed9b005..900f71c033 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -707,6 +707,7 @@ extern bool pgstat_lock_entry_shared(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_unlock_entry(PgStat_EntryRef *entry_ref);
extern bool pgstat_drop_entry(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_drop_all_entries(void);
+extern void pgstat_drop_entries_of_kind(PgStat_Kind kind);
extern PgStat_EntryRef *pgstat_get_entry_ref_locked(PgStat_Kind kind, Oid dboid, uint64 objid,
bool nowait);
extern void pgstat_reset_entry(PgStat_Kind kind, Oid dboid, uint64 objid, TimestampTz ts);
--
2.47.1
[application/octet-stream] 0003-Optionally-record-a-plan_id-in-PlannedStmt-to-identi.patch (44.7K, 4-0003-Optionally-record-a-plan_id-in-PlannedStmt-to-identi.patch)
download | inline diff:
From 5b5d84f3d5a25ef47fa9042985a8dfe33c76473c Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Tue, 31 Dec 2024 15:16:10 -0800
Subject: [PATCH 3/4] Optionally record a plan_id in PlannedStmt to identify
plan shape
When enabled via the new compute_plan_id GUC (default off), this utilizes
the existing treewalk in setrefs.c after planning to calculate a hash
(the "plan_id", or plan identifier) that can be used to identify
which plan was chosen.
The plan_id generally intends to be the same if a given EXPLAIN (without
ANALYZE) output is the same. The plan_id includes both the top-level plan
as well as all subplans. Execution statistics are excluded.
If enabled, the plan_id is shown for currently running queries in
pg_stat_activity, as well as recorded in EXPLAIN and auto_explain output.
Other in core users or extensions can use this facility to show or
accumulate statistics about the plans used by queries, to help identify
plan regressions, or drive plan management decisions.
Note that this commit intentionally does not include a facility to map
a given plan_id to the EXPLAIN text output - it is a assumed that users
can utilize the auto_explain extension to establish this mapping as
needed, or extensions can record this via the existing planner hook.
---
doc/src/sgml/config.sgml | 32 ++
doc/src/sgml/monitoring.sgml | 16 +
src/backend/catalog/system_views.sql | 1 +
src/backend/commands/explain.c | 17 +
src/backend/executor/execMain.c | 8 +-
src/backend/executor/execParallel.c | 1 +
src/backend/nodes/Makefile | 1 +
src/backend/nodes/meson.build | 1 +
src/backend/nodes/planjumble.c | 451 ++++++++++++++++++
src/backend/optimizer/plan/planner.c | 17 +
src/backend/optimizer/plan/setrefs.c | 8 +
src/backend/tcop/postgres.c | 1 +
src/backend/utils/activity/backend_status.c | 70 ++-
src/backend/utils/adt/pgstatfuncs.c | 7 +-
src/backend/utils/misc/guc_tables.c | 28 ++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/catalog/pg_proc.dat | 6 +-
src/include/nodes/pathnodes.h | 3 +
src/include/nodes/planjumble.h | 33 ++
src/include/nodes/plannodes.h | 4 +-
src/include/nodes/primnodes.h | 6 +-
src/include/utils/backend_status.h | 5 +
src/test/regress/expected/explain.out | 11 +
src/test/regress/expected/rules.out | 9 +-
src/test/regress/sql/explain.sql | 4 +
25 files changed, 722 insertions(+), 19 deletions(-)
create mode 100644 src/backend/nodes/planjumble.c
create mode 100644 src/include/nodes/planjumble.h
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fbdd6ce574..196d64efd8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8546,6 +8546,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-compute-plan-id" xreflabel="compute_plan_id">
+ <term><varname>compute_plan_id</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>compute_plan_id</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Enables in-core computation of a plan identifier.
+ Plan identifiers can be displayed in the <link
+ linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>
+ view or using <command>EXPLAIN</command>.
+ Note that an external module can alternatively be used if the
+ in-core plan identifier computation method is not acceptable.
+ In this case, in-core computation must be always disabled.
+ Valid values are <literal>off</literal> (always disabled),
+ <literal>on</literal> (always enabled) and <literal>regress</literal> which
+ has the same effect as <literal>on</literal>, except that the
+ query identifier is not shown in the <literal>EXPLAIN</literal> output
+ in order to facilitate automated regression testing.
+ The default is <literal>off</literal>.
+ </para>
+ <note>
+ <para>
+ To ensure that only one plan identifier is calculated and
+ displayed, extensions that calculate plan identifiers should
+ throw an error if a plan identifier has already been computed.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-statement-stats">
<term><varname>log_statement_stats</varname> (<type>boolean</type>)
<indexterm>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d0d176cc54..85b38ee642 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -972,6 +972,22 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>plan_id</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Identifier of this backend's most recent query plan. If
+ <structfield>state</structfield> is <literal>active</literal> this
+ field shows the identifier of the currently executing query plan. In
+ all other states, it shows the identifier of last query plan that
+ was executed. Plan identifiers are not computed by default so this
+ field will be null unless <xref linkend="guc-compute-plan-id"/>
+ parameter is enabled or a third-party module that computes plan
+ identifiers is configured.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>query</structfield> <type>text</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index da9a8fe99f..a26e00a792 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -880,6 +880,7 @@ CREATE VIEW pg_stat_activity AS
S.backend_xid,
s.backend_xmin,
S.query_id,
+ S.plan_id,
S.query,
S.backend_type
FROM pg_stat_get_activity(NULL) AS S
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a201ed3082..deeedf70cc 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -25,6 +25,7 @@
#include "nodes/extensible.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/planjumble.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -966,6 +967,22 @@ ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc)
ExplainPropertyInteger("Query Identifier", NULL, (int64)
queryDesc->plannedstmt->queryId, es);
}
+
+ /*
+ * COMPUTE_PLAN_ID_REGRESS means COMPUTE_PLAN_ID_YES, but we don't show
+ * the queryid in any of the EXPLAIN plans to keep stable the results
+ * generated by regression test suites.
+ */
+ if (es->verbose && queryDesc->plannedstmt->planId != UINT64CONST(0) &&
+ compute_plan_id != COMPUTE_PLAN_ID_REGRESS)
+ {
+ /*
+ * Output the queryid as an int64 rather than a uint64 so we match
+ * what would be seen in the BIGINT pg_stat_activity.plan_id column.
+ */
+ ExplainPropertyInteger("Plan Identifier", NULL, (int64)
+ queryDesc->plannedstmt->planId, es);
+ }
}
/*
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 1c12d6ebff..19becdd66c 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -120,13 +120,15 @@ ExecutorStart(QueryDesc *queryDesc, int eflags)
{
/*
* In some cases (e.g. an EXECUTE statement or an execute message with the
- * extended query protocol) the query_id won't be reported, so do it now.
+ * extended query protocol) the query_id and plan_id won't be reported,
+ * so do it now.
*
- * Note that it's harmless to report the query_id multiple times, as the
- * call will be ignored if the top level query_id has already been
+ * Note that it's harmless to report the identifiers multiple times, as the
+ * call will be ignored if the top level query_id / plan_id has already been
* reported.
*/
pgstat_report_query_id(queryDesc->plannedstmt->queryId, false);
+ pgstat_report_plan_id(queryDesc->plannedstmt->planId, queryDesc->plannedstmt->queryId, false);
if (ExecutorStart_hook)
(*ExecutorStart_hook) (queryDesc, eflags);
diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c
index 846ec727de..54cb22967e 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -174,6 +174,7 @@ ExecSerializePlan(Plan *plan, EState *estate)
pstmt = makeNode(PlannedStmt);
pstmt->commandType = CMD_SELECT;
pstmt->queryId = pgstat_get_my_query_id();
+ pstmt->planId = pgstat_get_my_plan_id();
pstmt->hasReturning = false;
pstmt->hasModifyingCTE = false;
pstmt->canSetTag = true;
diff --git a/src/backend/nodes/Makefile b/src/backend/nodes/Makefile
index 66bbad8e6e..1ab9345d28 100644
--- a/src/backend/nodes/Makefile
+++ b/src/backend/nodes/Makefile
@@ -25,6 +25,7 @@ OBJS = \
nodeFuncs.o \
outfuncs.o \
params.o \
+ planjumble.o \
print.o \
queryjumblefuncs.o \
read.o \
diff --git a/src/backend/nodes/meson.build b/src/backend/nodes/meson.build
index 49626f160e..4640e7a361 100644
--- a/src/backend/nodes/meson.build
+++ b/src/backend/nodes/meson.build
@@ -8,6 +8,7 @@ backend_sources += files(
'multibitmapset.c',
'nodeFuncs.c',
'params.c',
+ 'planjumble.c',
'print.c',
'read.c',
'tidbitmap.c',
diff --git a/src/backend/nodes/planjumble.c b/src/backend/nodes/planjumble.c
new file mode 100644
index 0000000000..a2f580d771
--- /dev/null
+++ b/src/backend/nodes/planjumble.c
@@ -0,0 +1,451 @@
+/*-------------------------------------------------------------------------
+ *
+ * planjumble.c
+ * Plan fingerprinting.
+ *
+ * Calculates the plan fingerprint for a given plan tree. Note this works
+ * in combination with the planner's setrefs functionality in order to
+ * walk the tree.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/nodes/planjumble.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "nodes/planjumble.h"
+#include "parser/parsetree.h"
+
+#define JUMBLE_SIZE 1024 /* query serialization buffer size */
+
+/* GUC parameters */
+int compute_plan_id = COMPUTE_PLAN_ID_OFF;
+
+#define JUMBLE_VALUE(item) \
+ AppendJumble(jstate, (const unsigned char *) &(item), sizeof(item))
+#define JUMBLE_STRING(str) \
+do { \
+ if (str) \
+ AppendJumble(jstate, (const unsigned char *) (str), strlen(str) + 1); \
+} while(0)
+
+/*
+ * Jumble the target relation of a scan or modify node
+ *
+ * This functions similarly to ExplainTargetRel.
+ */
+static void
+JumbleTargetRel(JumbleState *jstate, List* rtable, Plan *plan, Index rti)
+{
+ RangeTblEntry *rte = rt_fetch(rti, rtable);
+
+ switch (nodeTag(plan))
+ {
+ case T_SeqScan:
+ case T_SampleScan:
+ case T_IndexScan:
+ case T_IndexOnlyScan:
+ case T_BitmapHeapScan:
+ case T_TidScan:
+ case T_TidRangeScan:
+ case T_ForeignScan:
+ case T_CustomScan:
+ case T_ModifyTable:
+ /* Assert it's on a real relation */
+ Assert(rte->rtekind == RTE_RELATION);
+ JUMBLE_VALUE(rte->relid);
+ break;
+ case T_TableFuncScan:
+ {
+ TableFunc *tablefunc = ((TableFuncScan *) plan)->tablefunc;
+
+ Assert(rte->rtekind == RTE_TABLEFUNC);
+ JUMBLE_VALUE(tablefunc->functype);
+ }
+ break;
+ case T_ValuesScan:
+ Assert(rte->rtekind == RTE_VALUES);
+ break;
+ case T_CteScan:
+ /* Assert it's on a non-self-reference CTE */
+ Assert(rte->rtekind == RTE_CTE);
+ Assert(!rte->self_reference);
+ JUMBLE_STRING(rte->ctename);
+ break;
+ case T_NamedTuplestoreScan:
+ Assert(rte->rtekind == RTE_NAMEDTUPLESTORE);
+ JUMBLE_STRING(rte->enrname);
+ break;
+ case T_WorkTableScan:
+ /* Assert it's on a self-reference CTE */
+ Assert(rte->rtekind == RTE_CTE);
+ Assert(rte->self_reference);
+ JUMBLE_STRING(rte->ctename);
+ break;
+ default:
+ break;
+ }
+}
+
+/*
+ * Jumble the target of a Scan node
+ */
+static void
+JumbleScanTarget(JumbleState *jstate, List* rtable, Scan *scan)
+{
+ JumbleTargetRel(jstate, rtable, (Plan *) scan, scan->scanrelid);
+}
+
+/*
+ * JumblePlanNode: Append significant information to the plan identifier jumble
+ *
+ * Note this intentionally doesn't descend into child plan nodes, since the caller
+ * already takes care of that.
+ */
+void
+JumblePlanNode(JumbleState *jstate, List* rtable, Plan *plan)
+{
+ JUMBLE_VALUE(nodeTag(plan));
+ JumbleNode(jstate, (Node *) plan->qual);
+ JumbleNode(jstate, (Node *) plan->targetlist);
+
+ /*
+ * Plan-type-specific fixes
+ */
+ switch (nodeTag(plan))
+ {
+ case T_SeqScan:
+ {
+ SeqScan *splan = (SeqScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ }
+ break;
+ case T_SampleScan:
+ {
+ SampleScan *splan = (SampleScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ /* TODO: It may be worth jumbling the properties of splan->tablesample */
+ }
+ break;
+ case T_IndexScan:
+ {
+ IndexScan *splan = (IndexScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JUMBLE_VALUE(splan->indexid);
+ JumbleNode(jstate, (Node *) splan->indexqual);
+ /* Skip splan->indexqualorig */
+ JumbleNode(jstate, (Node *) splan->indexorderby);
+ /* Skip splan->indexorderbyorig */
+ JUMBLE_VALUE(splan->indexorderdir);
+ }
+ break;
+ case T_IndexOnlyScan:
+ {
+ IndexOnlyScan *splan = (IndexOnlyScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JUMBLE_VALUE(splan->indexid);
+ JumbleNode(jstate, (Node *) splan->indexqual);
+ /* Skip splan->recheckqual */
+ JumbleNode(jstate, (Node *) splan->indexorderby);
+ /* Skip splan->indextlist */
+ JUMBLE_VALUE(splan->indexorderdir);
+ }
+ break;
+ case T_BitmapIndexScan:
+ {
+ BitmapIndexScan *splan = (BitmapIndexScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JUMBLE_VALUE(splan->indexid);
+ JumbleNode(jstate, (Node *) splan->indexqual);
+ /* Skip splan->indexqualorig */
+ }
+ break;
+ case T_BitmapHeapScan:
+ {
+ BitmapHeapScan *splan = (BitmapHeapScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ /* Skip splan->bitmapqualorig */
+ }
+ break;
+ case T_TidScan:
+ {
+ TidScan *splan = (TidScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JumbleNode(jstate, (Node *) splan->tidquals);
+ }
+ break;
+ case T_TidRangeScan:
+ {
+ TidRangeScan *splan = (TidRangeScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JumbleNode(jstate, (Node *) splan->tidrangequals);
+ }
+ break;
+ case T_SubqueryScan:
+ {
+ SubqueryScan *splan = (SubqueryScan *) plan;
+ /* TODO: JumbleScanHeader currently doesn't jumble the subplan name */
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ /* We rely on the caller to descend into the actual subplans */
+ }
+ break;
+ case T_FunctionScan:
+ {
+ FunctionScan *splan = (FunctionScan *) plan;
+
+ /*
+ * If the expression is still a call of a single function,
+ * we can jumble the OID of the function. Otherwise, punt.
+ * (Even if it was a single function call originally, the
+ * optimizer could have simplified it away.)
+ */
+ if (list_length(splan->functions) == 1)
+ {
+ RangeTblFunction *rtfunc = (RangeTblFunction *) linitial(splan->functions);
+
+ if (IsA(rtfunc->funcexpr, FuncExpr))
+ {
+ FuncExpr *funcexpr = (FuncExpr *) rtfunc->funcexpr;
+ Oid funcid = funcexpr->funcid;
+ JUMBLE_VALUE(funcid);
+ }
+ }
+ }
+ break;
+ case T_TableFuncScan:
+ {
+ TableFuncScan *splan = (TableFuncScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ /* TODO: Should we jumble splan->tablefunc? */
+ }
+ break;
+ case T_ValuesScan:
+ {
+ ValuesScan *splan = (ValuesScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ JumbleNode(jstate, (Node *) splan->values_lists);
+ }
+ break;
+ case T_CteScan:
+ {
+ CteScan *splan = (CteScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ }
+ break;
+ case T_NamedTuplestoreScan:
+ {
+ NamedTuplestoreScan *splan = (NamedTuplestoreScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ }
+ break;
+ case T_WorkTableScan:
+ {
+ WorkTableScan *splan = (WorkTableScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ }
+ break;
+ case T_ForeignScan:
+ {
+ ForeignScan *splan = (ForeignScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ /* TODO: Should we jumble any FDW-specific information here, like EXPLAIN? */
+ }
+ break;
+ case T_CustomScan:
+ {
+ ForeignScan *splan = (ForeignScan *) plan;
+ JumbleScanTarget(jstate, rtable, &splan->scan);
+ }
+ break;
+ case T_NestLoop:
+ {
+ NestLoop *jplan = (NestLoop *) plan;
+ JUMBLE_VALUE(jplan->join.jointype);
+ /* Skip jplan->join.inner_unique */
+ JumbleNode(jstate, (Node *) jplan->join.joinqual);
+ JumbleNode(jstate, (Node *) jplan->nestParams);
+ }
+ break;
+ case T_MergeJoin:
+ {
+ MergeJoin *jplan = (MergeJoin *) plan;
+ JUMBLE_VALUE(jplan->join.jointype);
+ /* Skip jplan->join.inner_unique */
+ JumbleNode(jstate, (Node *) jplan->join.joinqual);
+ JumbleNode(jstate, (Node *) jplan->mergeclauses);
+ JumbleNode(jstate, (Node *) jplan->mergeFamilies);
+ JumbleNode(jstate, (Node *) jplan->mergeCollations);
+ JumbleNode(jstate, (Node *) jplan->mergeReversals);
+ JumbleNode(jstate, (Node *) jplan->mergeNullsFirst);
+ }
+ break;
+ case T_HashJoin:
+ {
+ HashJoin *jplan = (HashJoin *) plan;
+ JUMBLE_VALUE(jplan->join.jointype);
+ /* Skip jplan->join.inner_unique */
+ JumbleNode(jstate, (Node *) jplan->join.joinqual);
+ JumbleNode(jstate, (Node *) jplan->hashclauses);
+ JumbleNode(jstate, (Node *) jplan->hashoperators);
+ JumbleNode(jstate, (Node *) jplan->hashcollations);
+ JumbleNode(jstate, (Node *) jplan->hashkeys);
+ }
+ break;
+ case T_Gather:
+ {
+ Gather *gplan = (Gather *) plan;
+ JUMBLE_VALUE(gplan->num_workers);
+ /* Skip all other fields */
+ }
+ break;
+ case T_GatherMerge:
+ {
+ GatherMerge *gplan = (GatherMerge *) plan;
+ JUMBLE_VALUE(gplan->num_workers);
+ /* Skip all other fields */
+ }
+ break;
+ case T_Hash:
+ {
+ Hash *hplan = (Hash *) plan;
+ JumbleNode(jstate, (Node *) hplan->hashkeys);
+ /* Skip all other fields */
+ break;
+ }
+ break;
+ case T_Memoize:
+ {
+ Memoize *mplan = (Memoize *) plan;
+ JumbleNode(jstate, (Node *) mplan->param_exprs);
+ JUMBLE_VALUE(mplan->binary_mode);
+ /* Skip all other fields */
+ }
+ break;
+ case T_Material:
+ /* Materialize node has no fields of its own */
+ break;
+ case T_Sort:
+ {
+ Sort *splan = (Sort *) plan;
+ int i;
+ for (i = 0; i < splan->numCols; i++)
+ {
+ JUMBLE_VALUE(splan->sortColIdx[i]);
+ JUMBLE_VALUE(splan->sortOperators[i]);
+ JUMBLE_VALUE(splan->collations[i]);
+ JUMBLE_VALUE(splan->nullsFirst[i]);
+ }
+ }
+ break;
+ case T_IncrementalSort:
+ {
+ IncrementalSort *splan = (IncrementalSort *) plan;
+ int i;
+ for (i = 0; i < splan->sort.numCols; i++)
+ {
+ JUMBLE_VALUE(splan->sort.sortColIdx[i]);
+ JUMBLE_VALUE(splan->sort.sortOperators[i]);
+ JUMBLE_VALUE(splan->sort.collations[i]);
+ JUMBLE_VALUE(splan->sort.nullsFirst[i]);
+ }
+ JUMBLE_VALUE(splan->nPresortedCols);
+ }
+ break;
+ case T_Unique:
+ /* Skip all Unique node fields since EXPLAIN does not show them either */
+ break;
+ case T_SetOp:
+ {
+ SetOp *splan = (SetOp *) plan;
+ JUMBLE_VALUE(splan->cmd);
+ JUMBLE_VALUE(splan->strategy);
+ /* Skip all other fields since EXPLAIN does not show them either */
+ }
+ break;
+ case T_LockRows:
+ /* Skip all LockRows node fields since EXPLAIN does not show them either */
+ break;
+ case T_Limit:
+ /* Skip all Limit node fields since EXPLAIN does not show them either */
+ break;
+ case T_Agg:
+ {
+ Agg *agg = (Agg *) plan;
+ JUMBLE_VALUE(agg->aggstrategy);
+ JUMBLE_VALUE(agg->aggsplit);
+ /* Skip all other fields since EXPLAIN does not show them either */
+ }
+ break;
+ case T_Group:
+ {
+ Group *gplan = (Group *) plan;
+ int i;
+ for (i = 0; i < gplan->numCols; i++)
+ {
+ JUMBLE_VALUE(gplan->grpColIdx[i]);
+ JUMBLE_VALUE(gplan->grpOperators[i]);
+ JUMBLE_VALUE(gplan->grpCollations[i]);
+ }
+ }
+ break;
+ case T_WindowAgg:
+ {
+ WindowAgg *wplan = (WindowAgg *) plan;
+ JumbleNode(jstate, (Node *) wplan->runConditionOrig);
+ /* Skip all other fields since EXPLAIN does not show them either */
+ }
+ break;
+ case T_Result:
+ {
+ Result *splan = (Result *) plan;
+ JumbleNode(jstate, splan->resconstantqual);
+ }
+ break;
+ case T_ProjectSet:
+ /* ProjectSet node has no fields of its own */
+ break;
+ case T_ModifyTable:
+ {
+ ModifyTable *splan = (ModifyTable *) plan;
+ ListCell *lc;
+ JUMBLE_VALUE(splan->operation);
+ foreach (lc, splan->resultRelations)
+ {
+ JumbleTargetRel(jstate, rtable, plan, lfirst_int(lc));
+ }
+ JUMBLE_VALUE(splan->onConflictAction);
+ foreach (lc, splan->arbiterIndexes)
+ {
+ JumbleTargetRel(jstate, rtable, plan, lfirst_int(lc));
+ }
+ JumbleNode(jstate, splan->onConflictWhere);
+ /* Skip all other fields since EXPLAIN does not show them either */
+ }
+ break;
+ case T_Append:
+ /* Descending into Append node children is handled by the caller */
+ break;
+ case T_MergeAppend:
+ /* Descending into MergeAppend node children is handled by the caller */
+ break;
+ case T_RecursiveUnion:
+ /* Skip all RecursiveUnion node fields since EXPLAIN does not show them either */
+ break;
+ case T_BitmapAnd:
+ /* Descending into BitmapAnd node children is handled by the caller */
+ break;
+ case T_BitmapOr:
+ /* Descending into BitmapOr node children is handled by the caller */
+ break;
+ default:
+ elog(ERROR, "unrecognized node type: %d",
+ (int) nodeTag(plan));
+ break;
+ }
+}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 7468961b01..16888d152e 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -34,6 +34,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/planjumble.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -532,6 +533,15 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
Assert(glob->finalrowmarks == NIL);
Assert(glob->resultRelations == NIL);
Assert(glob->appendRelations == NIL);
+
+ /*
+ * Initialize plan identifier jumble if needed
+ *
+ * Note the actual jumbling is done in the tree walk in set_plan_references
+ */
+ if (compute_plan_id == COMPUTE_PLAN_ID_ON)
+ glob->plan_jumble_state = InitializeJumbleState(false);
+
top_plan = set_plan_references(root, top_plan);
/* ... and the subplans (both regular subplans and initplans) */
Assert(list_length(glob->subplans) == list_length(glob->subroots));
@@ -570,6 +580,13 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
result->stmt_location = parse->stmt_location;
result->stmt_len = parse->stmt_len;
+ if (compute_plan_id == COMPUTE_PLAN_ID_ON)
+ {
+ result->planId = HashJumbleState(glob->plan_jumble_state);
+ pfree(glob->plan_jumble_state->jumble);
+ pfree(glob->plan_jumble_state);
+ }
+
result->jitFlags = PGJIT_NONE;
if (jit_enabled && jit_above_cost >= 0 &&
top_plan->total_cost > jit_above_cost)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 6d23df108d..c02f939690 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -19,6 +19,7 @@
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/planjumble.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/planmain.h"
@@ -1295,6 +1296,13 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
break;
}
+ /*
+ * If enabled, append significant information to the plan identifier jumble
+ * (we do this here since we're already walking the tree in a near-final state)
+ */
+ if (compute_plan_id == COMPUTE_PLAN_ID_ON)
+ JumblePlanNode(root->glob->plan_jumble_state, root->glob->finalrtable, plan);
+
/*
* Now recurse into child plans, if any
*
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8590278818..03a04a198e 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1106,6 +1106,7 @@ exec_simple_query(const char *query_string)
size_t cmdtaglen;
pgstat_report_query_id(0, true);
+ pgstat_report_plan_id(0, 0, true);
/*
* Get the command name for use in status display (it also becomes the
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bf33e33a4e..77f3ca14ff 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -379,6 +379,7 @@ pgstat_bestart(void)
lbeentry.st_progress_command = PROGRESS_COMMAND_INVALID;
lbeentry.st_progress_command_target = InvalidOid;
lbeentry.st_query_id = UINT64CONST(0);
+ lbeentry.st_plan_id = UINT64CONST(0);
/*
* we don't zero st_progress_param here to save cycles; nobody should
@@ -533,6 +534,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
/* st_xact_start_timestamp and wait_event_info are also disabled */
beentry->st_xact_start_timestamp = 0;
beentry->st_query_id = UINT64CONST(0);
+ beentry->st_plan_id = UINT64CONST(0);
proc->wait_event_info = 0;
PGSTAT_END_WRITE_ACTIVITY(beentry);
}
@@ -588,12 +590,15 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
beentry->st_state_start_timestamp = current_timestamp;
/*
- * If a new query is started, we reset the query identifier as it'll only
- * be known after parse analysis, to avoid reporting last query's
- * identifier.
+ * If a new query is started, we reset the query and plan identifier as it'll only
+ * be known after parse analysis / planning, to avoid reporting last query's
+ * identifiers.
*/
if (state == STATE_RUNNING)
+ {
beentry->st_query_id = UINT64CONST(0);
+ beentry->st_plan_id = UINT64CONST(0);
+ }
if (cmd_str != NULL)
{
@@ -644,6 +649,45 @@ pgstat_report_query_id(uint64 query_id, bool force)
PGSTAT_END_WRITE_ACTIVITY(beentry);
}
+/* --------
+ * pgstat_report_plan_id() -
+ *
+ * Called to update top-level plan identifier.
+ * --------
+ */
+void
+pgstat_report_plan_id(uint64 plan_id, uint64 query_id, bool force)
+{
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ /*
+ * if track_activities is disabled, st_plan_id should already have been
+ * reset
+ */
+ if (!beentry || !pgstat_track_activities)
+ return;
+
+ /*
+ * We only report the top-level plan identifiers. The stored plan_id is
+ * reset when a backend calls pgstat_report_activity(STATE_RUNNING), or
+ * with an explicit call to this function using the force flag. If the
+ * saved plan identifier is not zero or the query identifier is 0,
+ * it means that it's not a top-level command, so ignore the one provided
+ * unless it's an explicit call to reset the identifier.
+ */
+ if ((beentry->st_plan_id != 0 || query_id == 0) && !force)
+ return;
+
+ /*
+ * Update my status entry, following the protocol of bumping
+ * st_changecount before and after. We use a volatile pointer here to
+ * ensure the compiler doesn't try to get cute.
+ */
+ PGSTAT_BEGIN_WRITE_ACTIVITY(beentry);
+ beentry->st_plan_id = plan_id;
+ PGSTAT_END_WRITE_ACTIVITY(beentry);
+}
+
/* ----------
* pgstat_report_appname() -
@@ -1040,6 +1084,26 @@ pgstat_get_my_query_id(void)
return MyBEEntry->st_query_id;
}
+/* ----------
+ * pgstat_get_my_plan_id() -
+ *
+ * Return current backend's plan identifier.
+ */
+uint64
+pgstat_get_my_plan_id(void)
+{
+ if (!MyBEEntry)
+ return 0;
+
+ /*
+ * There's no need for a lock around pgstat_begin_read_activity /
+ * pgstat_end_read_activity here as it's only called from
+ * pg_stat_get_activity which is already protected, or from the same
+ * backend which means that there won't be concurrent writes.
+ */
+ return MyBEEntry->st_plan_id;
+}
+
/* ----------
* pgstat_get_backend_type_by_proc_number() -
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6fc34f7494..4a5fc94877 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -302,7 +302,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
Datum
pg_stat_get_activity(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_ACTIVITY_COLS 31
+#define PG_STAT_GET_ACTIVITY_COLS 32
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -613,6 +613,10 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[30] = true;
else
values[30] = UInt64GetDatum(beentry->st_query_id);
+ if (beentry->st_plan_id == 0)
+ nulls[31] = true;
+ else
+ values[31] = UInt64GetDatum(beentry->st_plan_id);
}
else
{
@@ -642,6 +646,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[28] = true;
nulls[29] = true;
nulls[30] = true;
+ nulls[31] = true;
}
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8cf1afbad2..5139ba094a 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -50,6 +50,7 @@
#include "libpq/auth.h"
#include "libpq/libpq.h"
#include "libpq/scram.h"
+#include "nodes/planjumble.h"
#include "nodes/queryjumble.h"
#include "optimizer/cost.h"
#include "optimizer/geqo.h"
@@ -309,6 +310,23 @@ static const struct config_enum_entry compute_query_id_options[] = {
{NULL, 0, false}
};
+/*
+ * Although only "on" and "off" are documented, we accept
+ * all the likely variants of "on" and "off".
+ */
+static const struct config_enum_entry compute_plan_id_options[] = {
+ {"regress", COMPUTE_PLAN_ID_REGRESS, false},
+ {"on", COMPUTE_PLAN_ID_ON, false},
+ {"off", COMPUTE_PLAN_ID_OFF, false},
+ {"true", COMPUTE_PLAN_ID_ON, true},
+ {"false", COMPUTE_PLAN_ID_OFF, true},
+ {"yes", COMPUTE_PLAN_ID_ON, true},
+ {"no", COMPUTE_PLAN_ID_OFF, true},
+ {"1", COMPUTE_PLAN_ID_ON, true},
+ {"0", COMPUTE_PLAN_ID_OFF, true},
+ {NULL, 0, false}
+};
+
/*
* Although only "on", "off", and "partition" are documented, we
* accept all the likely variants of "on" and "off".
@@ -4863,6 +4881,16 @@ struct config_enum ConfigureNamesEnum[] =
NULL, NULL, NULL
},
+ {
+ {"compute_plan_id", PGC_SUSET, STATS_MONITORING,
+ gettext_noop("Enables in-core computation of plan identifiers."),
+ NULL
+ },
+ &compute_plan_id,
+ COMPUTE_PLAN_ID_OFF, compute_plan_id_options,
+ NULL, NULL, NULL
+ },
+
{
{"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER,
gettext_noop("Enables the planner to use constraints to optimize queries."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index a2ac7575ca..70fa7f886f 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -649,6 +649,7 @@
# - Monitoring -
#compute_query_id = auto
+#compute_plan_id = off
#log_statement_stats = off
#log_parser_stats = off
#log_planner_stats = off
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2dcc2d42da..56feae2583 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5549,9 +5549,9 @@
proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,bool,text,bool,bool,int4,int8}',
- proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
+ proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,bool,text,bool,bool,int4,int8,int8}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id,plan_id}',
prosrc => 'pg_stat_get_activity' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 58748d2ca6..9068cb826e 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -163,6 +163,9 @@ typedef struct PlannerGlobal
/* partition descriptors */
PartitionDirectory partition_directory pg_node_attr(read_write_ignore);
+
+ /* optional jumble state for plan identifier claculation */
+ struct JumbleState *plan_jumble_state pg_node_attr(read_write_ignore);
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
diff --git a/src/include/nodes/planjumble.h b/src/include/nodes/planjumble.h
new file mode 100644
index 0000000000..d35b66aec4
--- /dev/null
+++ b/src/include/nodes/planjumble.h
@@ -0,0 +1,33 @@
+/*-------------------------------------------------------------------------
+ *
+ * planjumble.h
+ * Plan fingerprinting.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/include/nodes/planjumble.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PLANJUMBLE_H
+#define PLANJUMBLE_H
+
+#include "nodes/plannodes.h"
+#include "nodes/queryjumble.h"
+
+/* Values for the compute_plan_id GUC */
+enum ComputePlanIdType
+{
+ COMPUTE_PLAN_ID_OFF,
+ COMPUTE_PLAN_ID_ON,
+ COMPUTE_PLAN_ID_REGRESS,
+};
+
+/* GUC parameters */
+extern PGDLLIMPORT int compute_plan_id;
+
+extern void JumblePlanNode(JumbleState *jumble, List* rtable, Plan *plan);
+
+#endif /* PLANJUMBLE_H */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4633121689..2f06d1d496 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -53,6 +53,8 @@ typedef struct PlannedStmt
uint64 queryId; /* query identifier (copied from Query) */
+ uint64 planId; /* plan identifier (calculated if compute_plan_id is enabled, can also be set by plugins) */
+
bool hasReturning; /* is it insert|update|delete|merge RETURNING? */
bool hasModifyingCTE; /* has insert|update|delete|merge in WITH? */
@@ -813,7 +815,7 @@ typedef struct NestLoop
typedef struct NestLoopParam
{
- pg_node_attr(no_equal, no_query_jumble)
+ pg_node_attr(no_equal)
NodeTag type;
int paramno; /* number of the PARAM_EXEC Param to set */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b0ef1952e8..eac6511f81 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1058,8 +1058,6 @@ typedef struct SubLink
*/
typedef struct SubPlan
{
- pg_node_attr(no_query_jumble)
-
Expr xpr;
/* Fields copied from original SubLink: */
SubLinkType subLinkType; /* see above */
@@ -1090,8 +1088,8 @@ typedef struct SubPlan
List *parParam; /* indices of input Params from parent plan */
List *args; /* exprs to pass as parParam values */
/* Estimated execution costs: */
- Cost startup_cost; /* one-time setup cost */
- Cost per_call_cost; /* cost for each subplan evaluation */
+ Cost startup_cost pg_node_attr(query_jumble_ignore); /* one-time setup cost */
+ Cost per_call_cost pg_node_attr(query_jumble_ignore); /* cost for each subplan evaluation */
} SubPlan;
/*
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 4e8b39a66d..8bb01256dd 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -170,6 +170,9 @@ typedef struct PgBackendStatus
/* query identifier, optionally computed using post_parse_analyze_hook */
uint64 st_query_id;
+
+ /* plan identifier, optionally computed after planning */
+ uint64 st_plan_id;
} PgBackendStatus;
@@ -316,6 +319,7 @@ extern void pgstat_clear_backend_activity_snapshot(void);
/* Activity reporting functions */
extern void pgstat_report_activity(BackendState state, const char *cmd_str);
extern void pgstat_report_query_id(uint64 query_id, bool force);
+extern void pgstat_report_plan_id(uint64 query_id, uint64 plan_id, bool force);
extern void pgstat_report_tempfile(size_t filesize);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
@@ -323,6 +327,7 @@ extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer,
int buflen);
extern uint64 pgstat_get_my_query_id(void);
+extern uint64 pgstat_get_my_plan_id(void);
extern BackendType pgstat_get_backend_type_by_proc_number(ProcNumber procNumber);
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index ee31e41d50..8bfa3c1a5f 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -699,6 +699,17 @@ select explain_filter('explain (verbose) create table test_ctas as select 1');
Query Identifier: N
(3 rows)
+-- Test compute_plan_id
+set compute_plan_id = on;
+select explain_filter('explain (verbose) select * from int8_tbl i8');
+ explain_filter
+----------------------------------------------------------------
+ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ Output: q1, q2
+ Query Identifier: N
+ Plan Identifier: N
+(4 rows)
+
-- Test SERIALIZE option
select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8');
explain_filter
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 3014d047fe..c041a49dea 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1760,9 +1760,10 @@ pg_stat_activity| SELECT s.datid,
s.backend_xid,
s.backend_xmin,
s.query_id,
+ s.plan_id,
s.query,
s.backend_type
- FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id, plan_id)
LEFT JOIN pg_database d ON ((s.datid = d.oid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1886,7 +1887,7 @@ pg_stat_gssapi| SELECT pid,
gss_princ AS principal,
gss_enc AS encrypted,
gss_delegation AS credentials_delegated
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+ FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id, plan_id)
WHERE (client_port IS NOT NULL);
pg_stat_io| SELECT backend_type,
object,
@@ -2090,7 +2091,7 @@ pg_stat_replication| SELECT s.pid,
w.sync_priority,
w.sync_state,
w.reply_time
- FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id, plan_id)
JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, reply_time) ON ((s.pid = w.pid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_replication_slots| SELECT s.slot_name,
@@ -2124,7 +2125,7 @@ pg_stat_ssl| SELECT pid,
ssl_client_dn AS client_dn,
ssl_client_serial AS client_serial,
ssl_issuer_dn AS issuer_dn
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
+ FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id, plan_id)
WHERE (client_port IS NOT NULL);
pg_stat_subscription| SELECT su.oid AS subid,
su.subname,
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 0bafa87049..d787ad2cda 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -167,6 +167,10 @@ select explain_filter('explain (verbose) select * from int8_tbl i8');
select explain_filter('explain (verbose) declare test_cur cursor for select * from int8_tbl');
select explain_filter('explain (verbose) create table test_ctas as select 1');
+-- Test compute_plan_id
+set compute_plan_id = on;
+select explain_filter('explain (verbose) select * from int8_tbl i8');
+
-- Test SERIALIZE option
select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8');
select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8');
--
2.47.1
[application/octet-stream] 0001-Allow-using-jumbling-logic-outside-of-query-jumble-u.patch (4.9K, 5-0001-Allow-using-jumbling-logic-outside-of-query-jumble-u.patch)
download | inline diff:
From 23911df5bf9e9841b02a3008402c870a8b757258 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Tue, 31 Dec 2024 15:05:39 -0800
Subject: [PATCH 1/4] Allow using jumbling logic outside of query jumble unit
file
This can be useful either for jumbling expressions in other contexts
(e.g. to calculate a plan jumble), or to allow extensions to use
a modified jumbling logic more easily.
This intentionally supports the use case where a separate jumbling logic
does not care about recording constants, as the query jumble does.
---
src/backend/nodes/queryjumblefuncs.c | 59 +++++++++++++++++-----------
src/include/nodes/queryjumble.h | 6 +++
2 files changed, 41 insertions(+), 24 deletions(-)
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index e8bf95690b..0b3af4169a 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -51,10 +51,7 @@ int compute_query_id = COMPUTE_QUERY_ID_AUTO;
*/
bool query_id_enabled = false;
-static void AppendJumble(JumbleState *jstate,
- const unsigned char *item, Size size);
static void RecordConstLocation(JumbleState *jstate, int location);
-static void _jumbleNode(JumbleState *jstate, Node *node);
static void _jumbleA_Const(JumbleState *jstate, Node *node);
static void _jumbleList(JumbleState *jstate, Node *node);
static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
@@ -109,28 +106,42 @@ CleanQuerytext(const char *query, int *location, int *len)
}
JumbleState *
-JumbleQuery(Query *query)
+InitializeJumbleState(bool record_clocations)
{
- JumbleState *jstate = NULL;
-
- Assert(IsQueryIdEnabled());
-
- jstate = (JumbleState *) palloc(sizeof(JumbleState));
+ JumbleState *jstate = (JumbleState *) palloc0(sizeof(JumbleState));
/* Set up workspace for query jumbling */
jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
jstate->jumble_len = 0;
- jstate->clocations_buf_size = 32;
- jstate->clocations = (LocationLen *)
- palloc(jstate->clocations_buf_size * sizeof(LocationLen));
- jstate->clocations_count = 0;
- jstate->highest_extern_param_id = 0;
+
+ if (record_clocations)
+ {
+ jstate->clocations_buf_size = 32;
+ jstate->clocations = (LocationLen *)
+ palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+ }
+
+ return jstate;
+}
+
+uint64
+HashJumbleState(JumbleState *jstate)
+{
+ return DatumGetUInt64(hash_any_extended(jstate->jumble,
+ jstate->jumble_len,
+ 0));
+}
+
+JumbleState *
+JumbleQuery(Query *query)
+{
+ JumbleState *jstate = InitializeJumbleState(true);
+
+ Assert(IsQueryIdEnabled());
/* Compute query ID and mark the Query node with it */
- _jumbleNode(jstate, (Node *) query);
- query->queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
- jstate->jumble_len,
- 0));
+ JumbleNode(jstate, (Node *) query);
+ query->queryId = HashJumbleState(jstate);
/*
* If we are unlucky enough to get a hash of zero, use 1 instead for
@@ -164,7 +175,7 @@ EnableQueryId(void)
* AppendJumble: Append a value that is substantive in a given query to
* the current jumble.
*/
-static void
+void
AppendJumble(JumbleState *jstate, const unsigned char *item, Size size)
{
unsigned char *jumble = jstate->jumble;
@@ -205,7 +216,7 @@ static void
RecordConstLocation(JumbleState *jstate, int location)
{
/* -1 indicates unknown or undefined location */
- if (location >= 0)
+ if (location >= 0 && jstate->clocations_buf_size > 0)
{
/* enlarge array if needed */
if (jstate->clocations_count >= jstate->clocations_buf_size)
@@ -224,7 +235,7 @@ RecordConstLocation(JumbleState *jstate, int location)
}
#define JUMBLE_NODE(item) \
- _jumbleNode(jstate, (Node *) expr->item)
+ JumbleNode(jstate, (Node *) expr->item)
#define JUMBLE_LOCATION(location) \
RecordConstLocation(jstate, expr->location)
#define JUMBLE_FIELD(item) \
@@ -239,8 +250,8 @@ do { \
#include "queryjumblefuncs.funcs.c"
-static void
-_jumbleNode(JumbleState *jstate, Node *node)
+void
+JumbleNode(JumbleState *jstate, Node *node)
{
Node *expr = node;
@@ -305,7 +316,7 @@ _jumbleList(JumbleState *jstate, Node *node)
{
case T_List:
foreach(l, expr)
- _jumbleNode(jstate, lfirst(l));
+ JumbleNode(jstate, lfirst(l));
break;
case T_IntList:
foreach(l, expr)
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index f1c55c8067..7f36dd025d 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -83,4 +83,10 @@ IsQueryIdEnabled(void)
return query_id_enabled;
}
+/* Functions intended for other users of jumbling (e.g. plan jumbling) */
+extern JumbleState *InitializeJumbleState(bool record_clocations);
+extern void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size);
+extern void JumbleNode(JumbleState *jstate, Node *node);
+extern uint64 HashJumbleState(JumbleState *jstate);
+
#endif /* QUERYJUMBLE_H */
--
2.47.1
[application/octet-stream] 0004-Add-pg_stat_plans-contrib-extension.patch (70.0K, 6-0004-Add-pg_stat_plans-contrib-extension.patch)
download | inline diff:
From 9840edbbfb22e4c6ffbbf6a5eb80aa863a95edf6 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Thu, 2 Jan 2025 10:47:50 -0800
Subject: [PATCH 4/4] Add pg_stat_plans contrib extension
This extension allows tracking per-plan call counts and execution time,
as well as capturing the plan text, aka EXPLAIN (COSTS OFF), for the
first execution of a given plan. This utilize the compute_plan_id
functionality for tracking different plans.
---
contrib/Makefile | 1 +
contrib/meson.build | 1 +
contrib/pg_stat_plans/Makefile | 29 +
contrib/pg_stat_plans/expected/cleanup.out | 1 +
contrib/pg_stat_plans/expected/privileges.out | 125 +++
contrib/pg_stat_plans/expected/select.out | 262 ++++++
contrib/pg_stat_plans/meson.build | 43 +
contrib/pg_stat_plans/pg_stat_plans--1.0.sql | 32 +
contrib/pg_stat_plans/pg_stat_plans.c | 743 ++++++++++++++++++
contrib/pg_stat_plans/pg_stat_plans.conf | 3 +
contrib/pg_stat_plans/pg_stat_plans.control | 5 +
contrib/pg_stat_plans/sql/cleanup.sql | 1 +
contrib/pg_stat_plans/sql/privileges.sql | 59 ++
contrib/pg_stat_plans/sql/select.sql | 67 ++
doc/src/sgml/contrib.sgml | 1 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/pgstatplans.sgml | 413 ++++++++++
17 files changed, 1787 insertions(+)
create mode 100644 contrib/pg_stat_plans/Makefile
create mode 100644 contrib/pg_stat_plans/expected/cleanup.out
create mode 100644 contrib/pg_stat_plans/expected/privileges.out
create mode 100644 contrib/pg_stat_plans/expected/select.out
create mode 100644 contrib/pg_stat_plans/meson.build
create mode 100644 contrib/pg_stat_plans/pg_stat_plans--1.0.sql
create mode 100644 contrib/pg_stat_plans/pg_stat_plans.c
create mode 100644 contrib/pg_stat_plans/pg_stat_plans.conf
create mode 100644 contrib/pg_stat_plans/pg_stat_plans.control
create mode 100644 contrib/pg_stat_plans/sql/cleanup.sql
create mode 100644 contrib/pg_stat_plans/sql/privileges.sql
create mode 100644 contrib/pg_stat_plans/sql/select.sql
create mode 100644 doc/src/sgml/pgstatplans.sgml
diff --git a/contrib/Makefile b/contrib/Makefile
index 952855d9b6..8de010afde 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -34,6 +34,7 @@ SUBDIRS = \
pg_freespacemap \
pg_logicalinspect \
pg_prewarm \
+ pg_stat_plans \
pg_stat_statements \
pg_surgery \
pg_trgm \
diff --git a/contrib/meson.build b/contrib/meson.build
index 159ff41555..430910fba4 100644
--- a/contrib/meson.build
+++ b/contrib/meson.build
@@ -49,6 +49,7 @@ subdir('pg_freespacemap')
subdir('pg_logicalinspect')
subdir('pg_prewarm')
subdir('pgrowlocks')
+subdir('pg_stat_plans')
subdir('pg_stat_statements')
subdir('pgstattuple')
subdir('pg_surgery')
diff --git a/contrib/pg_stat_plans/Makefile b/contrib/pg_stat_plans/Makefile
new file mode 100644
index 0000000000..e073db95ed
--- /dev/null
+++ b/contrib/pg_stat_plans/Makefile
@@ -0,0 +1,29 @@
+# contrib/pg_stat_plans/Makefile
+
+MODULE_big = pg_stat_plans
+OBJS = \
+ $(WIN32RES) \
+ pg_stat_plans.o
+
+EXTENSION = pg_stat_plans
+DATA = pg_stat_plans--1.0.sql
+PGFILEDESC = "pg_stat_plans - track per-plan call counts, execution times and EXPLAIN texts"
+
+LDFLAGS_SL += $(filter -lm, $(LIBS))
+
+REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_plans/pg_stat_plans.conf
+REGRESS = select privileges cleanup
+# Disabled because these tests require "shared_preload_libraries=pg_stat_plans",
+# which typical installcheck users do not have (e.g. buildfarm clients).
+NO_INSTALLCHECK = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_stat_plans
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_stat_plans/expected/cleanup.out b/contrib/pg_stat_plans/expected/cleanup.out
new file mode 100644
index 0000000000..51565617ce
--- /dev/null
+++ b/contrib/pg_stat_plans/expected/cleanup.out
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_plans;
diff --git a/contrib/pg_stat_plans/expected/privileges.out b/contrib/pg_stat_plans/expected/privileges.out
new file mode 100644
index 0000000000..3e21d6d701
--- /dev/null
+++ b/contrib/pg_stat_plans/expected/privileges.out
@@ -0,0 +1,125 @@
+--
+-- Only superusers and roles with privileges of the pg_read_all_stats role
+-- are allowed to see the plan text, queryid and planid of queries executed by
+-- other users. Other users can see the statistics.
+--
+CREATE ROLE regress_stats_superuser SUPERUSER;
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+GRANT pg_read_all_stats TO regress_stats_user2;
+SET ROLE regress_stats_superuser;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 1 AS "ONE";
+ ONE
+-----
+ 1
+(1 row)
+
+SET ROLE regress_stats_user1;
+SELECT 1+1 AS "TWO";
+ TWO
+-----
+ 2
+(1 row)
+
+--
+-- A superuser can read all columns of queries executed by others,
+-- including plan text, queryid and planid.
+--
+SET ROLE regress_stats_superuser;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+ rolname | queryid_bool | planid_bool | plan | calls
+-------------------------+--------------+-------------+------------------------------------------------------------------------------------+-------
+ regress_stats_superuser | t | t | Result | 1
+ regress_stats_superuser | t | t | Result | 1
+ regress_stats_superuser | t | t | Sort +| 0
+ | | | Sort Key: pg_authid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|
+ | | | -> Hash Join +|
+ | | | Hash Cond: (pg_stat_plans.userid = pg_authid.oid) +|
+ | | | -> Function Scan on pg_stat_plans +|
+ | | | -> Hash +|
+ | | | -> Seq Scan on pg_authid |
+ regress_stats_user1 | t | t | Result | 1
+(4 rows)
+
+--
+-- regress_stats_user1 has no privileges to read the plan text, queryid
+-- or planid of queries executed by others but can see statistics
+-- like calls and rows.
+--
+SET ROLE regress_stats_user1;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+ rolname | queryid_bool | planid_bool | plan | calls
+-------------------------+--------------+-------------+------------------------------------------------------------------------------------+-------
+ regress_stats_superuser | | | <insufficient privilege> | 1
+ regress_stats_superuser | | | <insufficient privilege> | 1
+ regress_stats_superuser | | | <insufficient privilege> | 1
+ regress_stats_user1 | t | t | Result | 1
+ regress_stats_user1 | t | t | Sort +| 0
+ | | | Sort Key: pg_authid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|
+ | | | -> Hash Join +|
+ | | | Hash Cond: (pg_stat_plans.userid = pg_authid.oid) +|
+ | | | -> Function Scan on pg_stat_plans +|
+ | | | -> Hash +|
+ | | | -> Seq Scan on pg_authid |
+(5 rows)
+
+--
+-- regress_stats_user2, with pg_read_all_stats role privileges, can
+-- read all columns, including plan text, queryid and planid, of queries
+-- executed by others.
+--
+SET ROLE regress_stats_user2;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+ rolname | queryid_bool | planid_bool | plan | calls
+-------------------------+--------------+-------------+------------------------------------------------------------------------------------+-------
+ regress_stats_superuser | t | t | Result | 1
+ regress_stats_superuser | t | t | Result | 1
+ regress_stats_superuser | t | t | Sort +| 1
+ | | | Sort Key: pg_authid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|
+ | | | -> Hash Join +|
+ | | | Hash Cond: (pg_stat_plans.userid = pg_authid.oid) +|
+ | | | -> Function Scan on pg_stat_plans +|
+ | | | -> Hash +|
+ | | | -> Seq Scan on pg_authid |
+ regress_stats_user1 | t | t | Result | 1
+ regress_stats_user1 | t | t | Sort +| 1
+ | | | Sort Key: pg_authid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|
+ | | | -> Hash Join +|
+ | | | Hash Cond: (pg_stat_plans.userid = pg_authid.oid) +|
+ | | | -> Function Scan on pg_stat_plans +|
+ | | | -> Hash +|
+ | | | -> Seq Scan on pg_authid |
+ regress_stats_user2 | t | t | Sort +| 0
+ | | | Sort Key: pg_authid.rolname, pg_stat_plans.plan COLLATE "C", pg_stat_plans.calls+|
+ | | | -> Hash Join +|
+ | | | Hash Cond: (pg_stat_plans.userid = pg_authid.oid) +|
+ | | | -> Function Scan on pg_stat_plans +|
+ | | | -> Hash +|
+ | | | -> Seq Scan on pg_authid |
+(6 rows)
+
+--
+-- cleanup
+--
+RESET ROLE;
+DROP ROLE regress_stats_superuser;
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/pg_stat_plans/expected/select.out b/contrib/pg_stat_plans/expected/select.out
new file mode 100644
index 0000000000..906d8ce90d
--- /dev/null
+++ b/contrib/pg_stat_plans/expected/select.out
@@ -0,0 +1,262 @@
+--
+-- SELECT statements
+--
+CREATE EXTENSION pg_stat_plans;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+--
+-- simple statements
+--
+SELECT 1 FROM pg_class LIMIT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT 1 FROM pg_class WHERE relname = 'pg_class';
+ ?column?
+----------
+ 1
+(1 row)
+
+SET enable_indexscan = off;
+SELECT 1 FROM pg_class WHERE relname = 'pg_class';
+ ?column?
+----------
+ 1
+(1 row)
+
+SET enable_indexscan = on;
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+ plan | calls
+--------------------------------------------------------------+-------
+ Bitmap Heap Scan on pg_class +| 1
+ Recheck Cond: (relname = 'pg_class'::name) +|
+ -> Bitmap Index Scan on pg_class_relname_nsp_index +|
+ Index Cond: (relname = 'pg_class'::name) |
+ Index Only Scan using pg_class_relname_nsp_index on pg_class+| 1
+ Index Cond: (relname = 'pg_class'::name) |
+ Limit +| 1
+ -> Seq Scan on pg_class |
+ Result | 1
+ Sort +| 0
+ Sort Key: pg_stat_plans.plan COLLATE "C" +|
+ -> Function Scan on pg_stat_plans |
+(5 rows)
+
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+--
+-- subplans and CTEs
+--
+WITH x AS MATERIALIZED (SELECT 1)
+SELECT * FROM x;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT a.attname,
+ (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
+ FROM pg_catalog.pg_attrdef d
+ WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
+ FROM pg_catalog.pg_attribute a
+ WHERE a.attrelid = 'pg_class'::regclass
+ ORDER BY attnum LIMIT 1;
+ attname | pg_get_expr
+----------+-------------
+ tableoid |
+(1 row)
+
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+ plan | calls
+-------------------------------------------------------------------------------+-------
+ CTE Scan on x +| 1
+ CTE x +|
+ -> Result |
+ Limit +| 1
+ -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a +|
+ Index Cond: (attrelid = '1259'::oid) +|
+ SubPlan 1 +|
+ -> Result +|
+ One-Time Filter: a.atthasdef +|
+ -> Seq Scan on pg_attrdef d +|
+ Filter: ((adrelid = a.attrelid) AND (adnum = a.attnum)) |
+ Result | 1
+ Sort +| 0
+ Sort Key: pg_stat_plans.plan COLLATE "C" +|
+ -> Function Scan on pg_stat_plans |
+(4 rows)
+
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+--
+-- partitoning
+--
+create table lp (a char) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in ('e', 'f');
+create table lp_ad partition of lp for values in ('a', 'd');
+create table lp_bc partition of lp for values in ('b', 'c');
+create table lp_g partition of lp for values in ('g');
+create table lp_null partition of lp for values in (null);
+select * from lp;
+ a
+---
+(0 rows)
+
+select * from lp where a > 'a' and a < 'd';
+ a
+---
+(0 rows)
+
+select * from lp where a > 'a' and a <= 'd';
+ a
+---
+(0 rows)
+
+select * from lp where a = 'a';
+ a
+---
+(0 rows)
+
+select * from lp where 'a' = a; /* commuted */
+ a
+---
+(0 rows)
+
+select * from lp where a is not null;
+ a
+---
+(0 rows)
+
+select * from lp where a is null;
+ a
+---
+(0 rows)
+
+select * from lp where a = 'a' or a = 'c';
+ a
+---
+(0 rows)
+
+select * from lp where a is not null and (a = 'a' or a = 'c');
+ a
+---
+(0 rows)
+
+select * from lp where a <> 'g';
+ a
+---
+(0 rows)
+
+select * from lp where a <> 'a' and a <> 'd';
+ a
+---
+(0 rows)
+
+select * from lp where a not in ('a', 'd');
+ a
+---
+(0 rows)
+
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+ plan | calls
+--------------------------------------------------------------------------------+-------
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) +|
+ -> Seq Scan on lp_bc lp_2 +|
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) |
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) +|
+ -> Seq Scan on lp_bc lp_2 +|
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) +|
+ -> Seq Scan on lp_default lp_3 +|
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) |
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))+|
+ -> Seq Scan on lp_bc lp_2 +|
+ Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) |
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ Filter: (a <> 'g'::bpchar) +|
+ -> Seq Scan on lp_bc lp_2 +|
+ Filter: (a <> 'g'::bpchar) +|
+ -> Seq Scan on lp_ef lp_3 +|
+ Filter: (a <> 'g'::bpchar) +|
+ -> Seq Scan on lp_default lp_4 +|
+ Filter: (a <> 'g'::bpchar) |
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ Filter: (a IS NOT NULL) +|
+ -> Seq Scan on lp_bc lp_2 +|
+ Filter: (a IS NOT NULL) +|
+ -> Seq Scan on lp_ef lp_3 +|
+ Filter: (a IS NOT NULL) +|
+ -> Seq Scan on lp_g lp_4 +|
+ Filter: (a IS NOT NULL) +|
+ -> Seq Scan on lp_default lp_5 +|
+ Filter: (a IS NOT NULL) |
+ Append +| 1
+ -> Seq Scan on lp_ad lp_1 +|
+ -> Seq Scan on lp_bc lp_2 +|
+ -> Seq Scan on lp_ef lp_3 +|
+ -> Seq Scan on lp_g lp_4 +|
+ -> Seq Scan on lp_null lp_5 +|
+ -> Seq Scan on lp_default lp_6 |
+ Append +| 1
+ -> Seq Scan on lp_bc lp_1 +|
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) +|
+ -> Seq Scan on lp_ef lp_2 +|
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) +|
+ -> Seq Scan on lp_g lp_3 +|
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) +|
+ -> Seq Scan on lp_default lp_4 +|
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) |
+ Append +| 1
+ -> Seq Scan on lp_bc lp_1 +|
+ Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) +|
+ -> Seq Scan on lp_default lp_2 +|
+ Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) |
+ Append +| 1
+ -> Seq Scan on lp_bc lp_1 +|
+ Filter: (a <> ALL ('{a,d}'::bpchar[])) +|
+ -> Seq Scan on lp_ef lp_2 +|
+ Filter: (a <> ALL ('{a,d}'::bpchar[])) +|
+ -> Seq Scan on lp_g lp_3 +|
+ Filter: (a <> ALL ('{a,d}'::bpchar[])) +|
+ -> Seq Scan on lp_default lp_4 +|
+ Filter: (a <> ALL ('{a,d}'::bpchar[])) |
+ Result | 1
+ Seq Scan on lp_ad lp +| 1
+ Filter: ('a'::bpchar = a) |
+ Seq Scan on lp_ad lp +| 1
+ Filter: (a = 'a'::bpchar) |
+ Seq Scan on lp_null lp +| 1
+ Filter: (a IS NULL) |
+ Sort +| 0
+ Sort Key: pg_stat_plans.plan COLLATE "C" +|
+ -> Function Scan on pg_stat_plans |
+(14 rows)
+
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/pg_stat_plans/meson.build b/contrib/pg_stat_plans/meson.build
new file mode 100644
index 0000000000..3bd884d960
--- /dev/null
+++ b/contrib/pg_stat_plans/meson.build
@@ -0,0 +1,43 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+pg_stat_plans_sources = files(
+ 'pg_stat_plans.c',
+)
+
+if host_system == 'windows'
+ pg_stat_plans_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'pg_stat_plans',
+ '--FILEDESC', 'pg_stat_plans - track per-plan call counts, execution times and EXPLAIN texts',])
+endif
+
+pg_stat_plans = shared_module('pg_stat_plans',
+ pg_stat_plans_sources,
+ kwargs: contrib_mod_args + {
+ 'dependencies': contrib_mod_args['dependencies'],
+ },
+)
+contrib_targets += pg_stat_plans
+
+install_data(
+ 'pg_stat_plans.control',
+ 'pg_stat_plans--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'pg_stat_plans',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'select',
+ 'privileges',
+ 'cleanup',
+ ],
+ 'regress_args': ['--temp-config', files('pg_stat_plans.conf')],
+ # Disabled because these tests require
+ # "shared_preload_libraries=pg_stat_plans", which typical
+ # runningcheck users do not have (e.g. buildfarm clients).
+ 'runningcheck': false,
+ }
+}
diff --git a/contrib/pg_stat_plans/pg_stat_plans--1.0.sql b/contrib/pg_stat_plans/pg_stat_plans--1.0.sql
new file mode 100644
index 0000000000..412d9e73ae
--- /dev/null
+++ b/contrib/pg_stat_plans/pg_stat_plans--1.0.sql
@@ -0,0 +1,32 @@
+/* contrib/pg_stat_plans/pg_stat_plans--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_stat_plans" to load this file. \quit
+
+-- Register functions.
+CREATE FUNCTION pg_stat_plans_reset()
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_plans(IN showplan boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT planid bigint,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT plan text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_plans_1_0'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+-- Register a view on the function for ease of use.
+CREATE VIEW pg_stat_plans AS
+ SELECT * FROM pg_stat_plans(true);
+
+GRANT SELECT ON pg_stat_plans TO PUBLIC;
+
+-- Don't want this to be available to non-superusers.
+REVOKE ALL ON FUNCTION pg_stat_plans_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_plans/pg_stat_plans.c b/contrib/pg_stat_plans/pg_stat_plans.c
new file mode 100644
index 0000000000..318a7cddc7
--- /dev/null
+++ b/contrib/pg_stat_plans/pg_stat_plans.c
@@ -0,0 +1,743 @@
+/*--------------------------------------------------------------------------
+ *
+ * pg_stat_plans.c
+ * Track per-plan call counts, execution times and EXPLAIN texts
+ * across a whole database cluster.
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * contrib/pg_stat_plans/pg_stat_plans.c
+ *
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/parallel.h"
+#include "catalog/pg_authid.h"
+#include "commands/explain.h"
+#include "common/hashfn.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "nodes/planjumble.h"
+#include "pgstat.h"
+#include "optimizer/planner.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/pgstat_internal.h"
+#include "utils/snapmgr.h"
+
+PG_MODULE_MAGIC;
+
+/* Current nesting depth of planner/ExecutorRun/ProcessUtility calls */
+static int nesting_level = 0;
+
+/* Saved hook values */
+static planner_hook_type prev_planner_hook = NULL;
+static ExecutorStart_hook_type prev_ExecutorStart = NULL;
+static ExecutorRun_hook_type prev_ExecutorRun = NULL;
+static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
+static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
+
+/*---- GUC variables ----*/
+
+typedef enum
+{
+ PGSP_TRACK_NONE, /* track no plans */
+ PGSP_TRACK_TOP, /* only plans for top level statements */
+ PGSP_TRACK_ALL, /* all plans, including for nested statements */
+} PGSPTrackLevel;
+
+static const struct config_enum_entry track_options[] =
+{
+ {"none", PGSP_TRACK_NONE, false},
+ {"top", PGSP_TRACK_TOP, false},
+ {"all", PGSP_TRACK_ALL, false},
+ {NULL, 0, false}
+};
+
+static int pgsp_max = 5000; /* max # plans to track */
+static int pgsp_max_size = 2048; /* max size of plan text to track (in bytes) */
+static int pgsp_track = PGSP_TRACK_TOP; /* tracking level */
+
+#define pgsp_enabled(level) \
+ (!IsParallelWorker() && \
+ (compute_plan_id != COMPUTE_PLAN_ID_OFF) && \
+ (pgsp_track == PGSP_TRACK_ALL || \
+ (pgsp_track == PGSP_TRACK_TOP && (level) == 0)))
+
+#define USAGE_INCREASE 0.5 /* increase by this each time we report stats */
+#define USAGE_DECREASE_FACTOR (0.99) /* decreased every pgstat_dealloc_plans */
+#define USAGE_DEALLOC_PERCENT 5 /* free this % of entries at once */
+
+/*---- Function declarations ----*/
+
+PG_FUNCTION_INFO_V1(pg_stat_plans_reset);
+PG_FUNCTION_INFO_V1(pg_stat_plans_1_0);
+
+/* Structures for statistics of plans */
+typedef struct PgStatShared_PlanInfo
+{
+ /* key elements that identify a plan (together with the dboid) */
+ uint64 planid;
+ uint64 queryid;
+ Oid userid; /* userid is tracked to allow users to see their own query plans */
+
+ dsa_pointer plan_text; /* pointer to DSA memory containing plan text */
+ int plan_encoding; /* plan text encoding */
+} PgStatShared_PlanInfo;
+
+typedef struct PgStat_StatPlanEntry
+{
+ PgStat_Counter exec_count;
+ double exec_time;
+ double usage; /* Usage factor of the entry, used to prioritize which plans to age out */
+
+ /* Only used in shared structure, not in local pending stats */
+ PgStatShared_PlanInfo info;
+} PgStat_StatPlanEntry;
+
+typedef struct PgStatShared_Plan
+{
+ PgStatShared_Common header;
+ PgStat_StatPlanEntry stats;
+} PgStatShared_Plan;
+
+static bool plan_stats_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+
+static const PgStat_KindInfo plan_stats = {
+ .name = "plan_stats",
+ .fixed_amount = false,
+
+ /* We currently don't write to a file since plan texts would get lost (and just the stats on their own aren't that useful) */
+ .write_to_file = false,
+
+ /* Plan statistics are available system-wide to simplify monitoring scripts */
+ .accessed_across_databases = true,
+
+ .shared_size = sizeof(PgStatShared_Plan),
+ .shared_data_off = offsetof(PgStatShared_Plan, stats),
+ .shared_data_len = sizeof(((PgStatShared_Plan *) 0)->stats),
+ .pending_size = sizeof(PgStat_StatPlanEntry),
+ .flush_pending_cb = plan_stats_flush_cb,
+};
+
+/*
+ * Compute stats entry idx from query ID and plan ID with an 8-byte hash.
+ *
+ * Whilst we could theorically just use the plan ID here, we intentionally
+ * add the query ID into the mix to ease interpreting the data in combination
+ * with pg_stat_statements.
+ */
+#define PGSTAT_PLAN_IDX(query_id, plan_id, user_id) hash_combine64(query_id, hash_combine64(plan_id, user_id))
+
+/*
+ * Kind ID reserved for statistics of plans.
+ */
+#define PGSTAT_KIND_PLANS PGSTAT_KIND_EXPERIMENTAL /* TODO: Assign */
+
+/*
+ * Callback for stats handling
+ */
+static bool
+plan_stats_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStat_StatPlanEntry *localent;
+ PgStatShared_Plan *shfuncent;
+
+ localent = (PgStat_StatPlanEntry *) entry_ref->pending;
+ shfuncent = (PgStatShared_Plan *) entry_ref->shared_stats;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+ shfuncent->stats.exec_count += localent->exec_count;
+ shfuncent->stats.exec_time += localent->exec_time;
+ shfuncent->stats.usage += localent->usage;
+
+ pgstat_unlock_entry(entry_ref);
+
+ return true;
+}
+
+static char *
+pgsp_explain_plan(QueryDesc *queryDesc)
+{
+ ExplainState *es;
+ StringInfo es_str;
+
+ es = NewExplainState();
+ es_str = es->str;
+
+ /*
+ * We turn off COSTS since identical planids may have very different costs,
+ * and it could be misleading to only show the first recorded plan's costs.
+ */
+ es->costs = false;
+ es->format = EXPLAIN_FORMAT_TEXT;
+
+ ExplainBeginOutput(es);
+ ExplainPrintPlan(es, queryDesc);
+ ExplainEndOutput(es);
+
+ return es_str->data;
+}
+
+static void
+pgstat_gc_plan_memory()
+{
+ dshash_seq_status hstat;
+ PgStatShared_HashEntry *p;
+
+ /* dshash entry is not modified, take shared lock */
+ dshash_seq_init(&hstat, pgStatLocal.shared_hash, false);
+ while ((p = dshash_seq_next(&hstat)) != NULL)
+ {
+ PgStatShared_Common *header;
+ PgStat_StatPlanEntry *statent;
+
+ if (!p->dropped || p->key.kind != PGSTAT_KIND_PLANS)
+ continue;
+
+ header = dsa_get_address(pgStatLocal.dsa, p->body);
+
+ if (!LWLockConditionalAcquire(&header->lock, LW_EXCLUSIVE))
+ continue;
+
+ statent = (PgStat_StatPlanEntry *) pgstat_get_entry_data(PGSTAT_KIND_PLANS, header);
+
+ /* Clean up this entry's plan text allocation, if we haven't done so already */
+ if (DsaPointerIsValid(statent->info.plan_text))
+ {
+ dsa_free(pgStatLocal.dsa, statent->info.plan_text);
+ statent->info.plan_text = InvalidDsaPointer;
+
+ /* Allow removal of the shared stats entry */
+ pg_atomic_fetch_sub_u32(&p->refcount, 1);
+ }
+
+ LWLockRelease(&header->lock);
+ }
+ dshash_seq_term(&hstat);
+
+ // Encourage other backends to clean up dropped entry refs
+ pgstat_request_entry_refs_gc();
+}
+
+typedef struct PlanDeallocEntry
+{
+ PgStat_HashKey key;
+ double usage;
+} PlanDeallocEntry;
+
+/*
+ * list sort comparator for sorting into decreasing usage order
+ */
+static int
+entry_cmp_lru(const union ListCell *lhs, const union ListCell *rhs)
+{
+ double l_usage = ((PlanDeallocEntry *) lfirst(lhs))->usage;
+ double r_usage = ((PlanDeallocEntry *) lfirst(rhs))->usage;
+
+ if (l_usage > r_usage)
+ return -1;
+ else if (l_usage < r_usage)
+ return +1;
+ else
+ return 0;
+}
+
+static void
+pgstat_dealloc_plans()
+{
+ dshash_seq_status hstat;
+ PgStatShared_HashEntry *p;
+ List *entries = NIL;
+ ListCell *lc;
+ int nvictims;
+
+ /* dshash entry is not modified, take shared lock */
+ dshash_seq_init(&hstat, pgStatLocal.shared_hash, false);
+ while ((p = dshash_seq_next(&hstat)) != NULL)
+ {
+ PgStatShared_Common *header;
+ PgStat_StatPlanEntry *statent;
+ PlanDeallocEntry *entry;
+
+ if (p->dropped || p->key.kind != PGSTAT_KIND_PLANS)
+ continue;
+
+ header = dsa_get_address(pgStatLocal.dsa, p->body);
+
+ if (!LWLockConditionalAcquire(&header->lock, LW_EXCLUSIVE))
+ continue;
+
+ statent = (PgStat_StatPlanEntry *) pgstat_get_entry_data(PGSTAT_KIND_PLANS, header);
+ statent->usage *= USAGE_DECREASE_FACTOR;
+
+ entry = palloc(sizeof(PlanDeallocEntry));
+ entry->key = p->key;
+ entry->usage = statent->usage;
+
+ LWLockRelease(&header->lock);
+
+ entries = lappend(entries, entry);
+ }
+ dshash_seq_term(&hstat);
+
+ /* Sort by usage ascending (lowest used entries are last) */
+ list_sort(entries, entry_cmp_lru);
+
+ /* At a minimum, deallocate 10 entries to make it worth our while */
+ nvictims = Max(10, list_length(entries) * USAGE_DEALLOC_PERCENT / 100);
+ nvictims = Min(nvictims, list_length(entries));
+
+ /* Actually drop the entries */
+ for_each_from(lc, entries, list_length(entries) - nvictims)
+ {
+ PlanDeallocEntry *entry = lfirst(lc);
+ pgstat_drop_entry(entry->key.kind, entry->key.dboid, entry->key.objid);
+ }
+
+ /* Clean up our working memory immediately */
+ foreach(lc, entries)
+ {
+ PlanDeallocEntry *entry = lfirst(lc);
+ pfree(entry);
+ }
+ pfree(entries);
+}
+
+static void
+pgstat_gc_plans()
+{
+ dshash_seq_status hstat;
+ PgStatShared_HashEntry *p;
+ bool have_dropped_entries = false;
+ size_t plan_entry_count = 0;
+
+ /* TODO: Prevent concurrent GC cycles - flag an active GC run somehow */
+
+ /*
+ * Count our active entries, and whether there are any dropped entries we
+ * may need to clean up at the end.
+ */
+ dshash_seq_init(&hstat, pgStatLocal.shared_hash, false);
+ while ((p = dshash_seq_next(&hstat)) != NULL)
+ {
+ if (p->key.kind != PGSTAT_KIND_PLANS)
+ continue;
+
+ if (p->dropped)
+ have_dropped_entries = true;
+ else
+ plan_entry_count++;
+ }
+ dshash_seq_term(&hstat);
+
+ /*
+ * If we're over the limit, delete entries with lowest usage factor.
+ */
+ if (plan_entry_count > pgsp_max)
+ {
+ pgstat_dealloc_plans();
+ have_dropped_entries = true; /* Assume we did some work */
+ }
+
+ /* If there are dropped entries, clean up their plan memory if needed */
+ if (have_dropped_entries)
+ pgstat_gc_plan_memory();
+}
+
+static void
+pgstat_report_plan_stats(QueryDesc *queryDesc,
+ PgStat_Counter exec_count,
+ double exec_time)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Plan *shstatent;
+ PgStat_StatPlanEntry *statent;
+ bool newly_created;
+ uint64 queryId = queryDesc->plannedstmt->queryId;
+ uint64 planId = queryDesc->plannedstmt->planId;
+ Oid userid = GetUserId();
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_PLANS, MyDatabaseId,
+ PGSTAT_PLAN_IDX(queryId, planId, userid), &newly_created);
+
+ shstatent = (PgStatShared_Plan *) entry_ref->shared_stats;
+ statent = &shstatent->stats;
+
+ if (newly_created)
+ {
+ char *plan = pgsp_explain_plan(queryDesc);
+ size_t plan_size = Min(strlen(plan), pgsp_max_size);
+
+ (void) pgstat_lock_entry(entry_ref, false);
+
+ /*
+ * We may be over the limit, so run GC now before saving entry
+ * (we do this whilst holding the lock on the new entry so we don't remove it by accident)
+ */
+ pgstat_gc_plans();
+
+ shstatent->stats.info.planid = planId;
+ shstatent->stats.info.queryid = queryId;
+ shstatent->stats.info.userid = userid;
+ shstatent->stats.info.plan_text = dsa_allocate(pgStatLocal.dsa, plan_size);
+ strlcpy(dsa_get_address(pgStatLocal.dsa, shstatent->stats.info.plan_text), plan, plan_size);
+
+ shstatent->stats.info.plan_encoding = GetDatabaseEncoding();
+
+ /* Increase refcount here so entry can't get released without us dropping the plan text */
+ pg_atomic_fetch_add_u32(&entry_ref->shared_entry->refcount, 1);
+
+ pgstat_unlock_entry(entry_ref);
+
+ pfree(plan);
+ }
+
+ statent->exec_count += exec_count;
+ statent->exec_time += exec_time;
+ statent->usage += USAGE_INCREASE;
+}
+
+/*
+ * Planner hook: forward to regular planner, but increase plan count and
+ * record query plan if needed.
+ */
+static PlannedStmt *
+pgsp_planner(Query *parse,
+ const char *query_string,
+ int cursorOptions,
+ ParamListInfo boundParams)
+{
+ PlannedStmt *result;
+
+ /*
+ * Increment the nesting level, to ensure that functions
+ * evaluated during planning are not seen as top-level calls.
+ */
+ nesting_level++;
+ PG_TRY();
+ {
+ if (prev_planner_hook)
+ result = prev_planner_hook(parse, query_string, cursorOptions,
+ boundParams);
+ else
+ result = standard_planner(parse, query_string, cursorOptions,
+ boundParams);
+ }
+ PG_FINALLY();
+ {
+ nesting_level--;
+ }
+ PG_END_TRY();
+
+ return result;
+}
+
+/*
+ * ExecutorStart hook: start up tracking if needed
+ */
+static void
+pgsp_ExecutorStart(QueryDesc *queryDesc, int eflags)
+{
+ uint64 queryId = queryDesc->plannedstmt->queryId;
+ uint64 planId = queryDesc->plannedstmt->planId;
+
+ if (prev_ExecutorStart)
+ prev_ExecutorStart(queryDesc, eflags);
+ else
+ standard_ExecutorStart(queryDesc, eflags);
+
+ if (queryId != UINT64CONST(0) && planId != UINT64CONST(0) &&
+ pgsp_enabled(nesting_level))
+ {
+ /* Record initial entry now, so plan text is available for currently running queries */
+ pgstat_report_plan_stats(queryDesc,
+ 0, /* executions are counted in pgsp_ExecutorEnd */
+ 0.0);
+
+ /*
+ * Set up to track total elapsed time in ExecutorRun. Make sure the
+ * space is allocated in the per-query context so it will go away at
+ * ExecutorEnd.
+ */
+ if (queryDesc->totaltime == NULL)
+ {
+ MemoryContext oldcxt;
+
+ oldcxt = MemoryContextSwitchTo(queryDesc->estate->es_query_cxt);
+ queryDesc->totaltime = InstrAlloc(1, INSTRUMENT_ALL, false);
+ MemoryContextSwitchTo(oldcxt);
+ }
+ }
+}
+
+/*
+ * ExecutorRun hook: all we need do is track nesting depth
+ */
+static void
+pgsp_ExecutorRun(QueryDesc *queryDesc, ScanDirection direction, uint64 count)
+{
+ nesting_level++;
+ PG_TRY();
+ {
+ if (prev_ExecutorRun)
+ prev_ExecutorRun(queryDesc, direction, count);
+ else
+ standard_ExecutorRun(queryDesc, direction, count);
+ }
+ PG_FINALLY();
+ {
+ nesting_level--;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * ExecutorFinish hook: all we need do is track nesting depth
+ */
+static void
+pgsp_ExecutorFinish(QueryDesc *queryDesc)
+{
+ nesting_level++;
+ PG_TRY();
+ {
+ if (prev_ExecutorFinish)
+ prev_ExecutorFinish(queryDesc);
+ else
+ standard_ExecutorFinish(queryDesc);
+ }
+ PG_FINALLY();
+ {
+ nesting_level--;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * ExecutorEnd hook: store results if needed
+ */
+static void
+pgsp_ExecutorEnd(QueryDesc *queryDesc)
+{
+ uint64 queryId = queryDesc->plannedstmt->queryId;
+ uint64 planId = queryDesc->plannedstmt->planId;
+
+ if (queryId != UINT64CONST(0) && planId != UINT64CONST(0) &&
+ queryDesc->totaltime && pgsp_enabled(nesting_level))
+ {
+ /*
+ * Make sure stats accumulation is done. (Note: it's okay if several
+ * levels of hook all do this.)
+ */
+ InstrEndLoop(queryDesc->totaltime);
+
+ pgstat_report_plan_stats(queryDesc,
+ 1,
+ queryDesc->totaltime->total * 1000.0 /* convert to msec */);
+ }
+
+ if (prev_ExecutorEnd)
+ prev_ExecutorEnd(queryDesc);
+ else
+ standard_ExecutorEnd(queryDesc);
+}
+
+/*
+ * Module load callback
+ */
+void
+_PG_init(void)
+{
+ /*
+ * In order to register for shared memory stats, we have to be loaded via
+ * shared_preload_libraries. If not, fall out without hooking into any of
+ * the main system. (We don't throw error here because it seems useful to
+ * allow the pg_stat_plans functions to be created even when the
+ * module isn't active. The functions must protect themselves against
+ * being called then, however.)
+ */
+ if (!process_shared_preload_libraries_in_progress)
+ return;
+
+ /*
+ * Inform the postmaster that we want to enable query_id calculation if
+ * compute_query_id is set to auto.
+ *
+ * Note that this does not apply to compute_plan_id, which must be
+ * enabled explicitly.
+ */
+ EnableQueryId();
+
+ /*
+ * Define (or redefine) custom GUC variables.
+ */
+ DefineCustomIntVariable("pg_stat_plans.max",
+ "Sets the maximum number of plans tracked by pg_stat_plans in shared memory.",
+ NULL,
+ &pgsp_max,
+ 5000,
+ 100,
+ INT_MAX / 2,
+ PGC_SIGHUP,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomIntVariable("pg_stat_plans.max_size",
+ "Sets the maximum size of plan texts tracked by pg_stat_plans in shared memory.",
+ NULL,
+ &pgsp_max_size,
+ 2048,
+ 100,
+ 1048576, /* 1MB hard limit */
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomEnumVariable("pg_stat_plans.track",
+ "Selects which plans are tracked by pg_stat_plans.",
+ NULL,
+ &pgsp_track,
+ PGSP_TRACK_TOP,
+ track_options,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ MarkGUCPrefixReserved("pg_stat_plans");
+
+ /*
+ * Install hooks.
+ */
+ prev_planner_hook = planner_hook;
+ planner_hook = pgsp_planner;
+ prev_ExecutorStart = ExecutorStart_hook;
+ ExecutorStart_hook = pgsp_ExecutorStart;
+ prev_ExecutorRun = ExecutorRun_hook;
+ ExecutorRun_hook = pgsp_ExecutorRun;
+ prev_ExecutorFinish = ExecutorFinish_hook;
+ ExecutorFinish_hook = pgsp_ExecutorFinish;
+ prev_ExecutorEnd = ExecutorEnd_hook;
+ ExecutorEnd_hook = pgsp_ExecutorEnd;
+
+ pgstat_register_kind(PGSTAT_KIND_PLANS, &plan_stats);
+}
+
+/*
+ * Reset statement statistics.
+ */
+Datum
+pg_stat_plans_reset(PG_FUNCTION_ARGS)
+{
+ pgstat_drop_entries_of_kind(PGSTAT_KIND_PLANS);
+
+ /* Free plan text memory and allow cleanup of dropped entries */
+ pgstat_gc_plan_memory();
+
+ PG_RETURN_VOID();
+}
+
+#define PG_STAT_PLANS_COLS 7
+
+Datum
+pg_stat_plans_1_0(PG_FUNCTION_ARGS)
+{
+ bool showplan = PG_GETARG_BOOL(0);
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ Oid userid = GetUserId();
+ bool is_allowed_role = false;
+
+ dshash_seq_status hstat;
+ PgStatShared_HashEntry *p;
+
+ /*
+ * Superusers or roles with the privileges of pg_read_all_stats members
+ * are allowed
+ */
+ is_allowed_role = has_privs_of_role(userid, ROLE_PG_READ_ALL_STATS);
+
+ /* stats kind must be registered already */
+ if (!pgstat_get_kind_info(PGSTAT_KIND_PLANS))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_plans must be loaded via \"shared_preload_libraries\"")));
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* dshash entry is not modified, take shared lock */
+ dshash_seq_init(&hstat, pgStatLocal.shared_hash, false);
+ while ((p = dshash_seq_next(&hstat)) != NULL)
+ {
+ PgStat_StatPlanEntry *statent;
+ Datum values[PG_STAT_PLANS_COLS];
+ bool nulls[PG_STAT_PLANS_COLS];
+ int i = 0;
+
+ if (p->dropped || p->key.kind != PGSTAT_KIND_PLANS)
+ continue;
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, 0, sizeof(nulls));
+
+ statent = pgstat_fetch_entry(p->key.kind, p->key.dboid, p->key.objid);
+
+ values[i++] = ObjectIdGetDatum(statent->info.userid);
+ values[i++] = ObjectIdGetDatum(p->key.dboid);
+ if (is_allowed_role || statent->info.userid == userid)
+ {
+ int64 queryid = statent->info.queryid;
+ int64 planid = statent->info.planid;
+ values[i++] = Int64GetDatumFast(queryid);
+ values[i++] = Int64GetDatumFast(planid);
+ }
+ else
+ {
+ nulls[i++] = true;
+ nulls[i++] = true;
+ }
+ values[i++] = Int64GetDatumFast(statent->exec_count);
+ values[i++] = Float8GetDatumFast(statent->exec_time);
+
+ if (showplan && (is_allowed_role || statent->info.userid == userid))
+ {
+ char *pstr = DsaPointerIsValid(statent->info.plan_text) ? dsa_get_address(pgStatLocal.dsa, statent->info.plan_text) : NULL;
+
+ if (pstr)
+ {
+ char *enc = pg_any_to_server(pstr, strlen(pstr), statent->info.plan_encoding);
+ values[i++] = CStringGetTextDatum(enc);
+
+ if (enc != pstr)
+ pfree(enc);
+ }
+ else
+ {
+ nulls[i++] = true;
+ }
+ }
+ else if (showplan)
+ {
+ values[i++] = CStringGetTextDatum("<insufficient privilege>");
+ }
+ else
+ {
+ nulls[i++] = true;
+ }
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+ dshash_seq_term(&hstat);
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_stat_plans/pg_stat_plans.conf b/contrib/pg_stat_plans/pg_stat_plans.conf
new file mode 100644
index 0000000000..bfe571d547
--- /dev/null
+++ b/contrib/pg_stat_plans/pg_stat_plans.conf
@@ -0,0 +1,3 @@
+shared_preload_libraries = 'pg_stat_plans'
+compute_plan_id = on
+compute_query_id = on
diff --git a/contrib/pg_stat_plans/pg_stat_plans.control b/contrib/pg_stat_plans/pg_stat_plans.control
new file mode 100644
index 0000000000..4db3a47239
--- /dev/null
+++ b/contrib/pg_stat_plans/pg_stat_plans.control
@@ -0,0 +1,5 @@
+# pg_stat_plans extension
+comment = 'track per-plan call counts, execution times and EXPLAIN texts'
+default_version = '1.0'
+module_pathname = '$libdir/pg_stat_plans'
+relocatable = true
diff --git a/contrib/pg_stat_plans/sql/cleanup.sql b/contrib/pg_stat_plans/sql/cleanup.sql
new file mode 100644
index 0000000000..51565617ce
--- /dev/null
+++ b/contrib/pg_stat_plans/sql/cleanup.sql
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_plans;
diff --git a/contrib/pg_stat_plans/sql/privileges.sql b/contrib/pg_stat_plans/sql/privileges.sql
new file mode 100644
index 0000000000..aaad72a655
--- /dev/null
+++ b/contrib/pg_stat_plans/sql/privileges.sql
@@ -0,0 +1,59 @@
+--
+-- Only superusers and roles with privileges of the pg_read_all_stats role
+-- are allowed to see the plan text, queryid and planid of queries executed by
+-- other users. Other users can see the statistics.
+--
+
+CREATE ROLE regress_stats_superuser SUPERUSER;
+CREATE ROLE regress_stats_user1;
+CREATE ROLE regress_stats_user2;
+GRANT pg_read_all_stats TO regress_stats_user2;
+
+SET ROLE regress_stats_superuser;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+SELECT 1 AS "ONE";
+
+SET ROLE regress_stats_user1;
+SELECT 1+1 AS "TWO";
+
+--
+-- A superuser can read all columns of queries executed by others,
+-- including plan text, queryid and planid.
+--
+
+SET ROLE regress_stats_superuser;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+
+--
+-- regress_stats_user1 has no privileges to read the plan text, queryid
+-- or planid of queries executed by others but can see statistics
+-- like calls and rows.
+--
+
+SET ROLE regress_stats_user1;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+
+--
+-- regress_stats_user2, with pg_read_all_stats role privileges, can
+-- read all columns, including plan text, queryid and planid, of queries
+-- executed by others.
+--
+
+SET ROLE regress_stats_user2;
+SELECT r.rolname, ss.queryid <> 0 AS queryid_bool, ss.planid <> 0 AS planid_bool, ss.plan, ss.calls
+ FROM pg_stat_plans ss JOIN pg_roles r ON ss.userid = r.oid
+ ORDER BY r.rolname, ss.plan COLLATE "C", ss.calls;
+
+--
+-- cleanup
+--
+
+RESET ROLE;
+DROP ROLE regress_stats_superuser;
+DROP ROLE regress_stats_user1;
+DROP ROLE regress_stats_user2;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_plans/sql/select.sql b/contrib/pg_stat_plans/sql/select.sql
new file mode 100644
index 0000000000..f0e803ad70
--- /dev/null
+++ b/contrib/pg_stat_plans/sql/select.sql
@@ -0,0 +1,67 @@
+--
+-- SELECT statements
+--
+
+CREATE EXTENSION pg_stat_plans;
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+
+--
+-- simple statements
+--
+
+SELECT 1 FROM pg_class LIMIT 1;
+
+SELECT 1 FROM pg_class WHERE relname = 'pg_class';
+
+SET enable_indexscan = off;
+SELECT 1 FROM pg_class WHERE relname = 'pg_class';
+SET enable_indexscan = on;
+
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+
+--
+-- subplans and CTEs
+--
+
+WITH x AS MATERIALIZED (SELECT 1)
+SELECT * FROM x;
+
+SELECT a.attname,
+ (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
+ FROM pg_catalog.pg_attrdef d
+ WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
+ FROM pg_catalog.pg_attribute a
+ WHERE a.attrelid = 'pg_class'::regclass
+ ORDER BY attnum LIMIT 1;
+
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
+
+--
+-- partitoning
+--
+
+create table lp (a char) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in ('e', 'f');
+create table lp_ad partition of lp for values in ('a', 'd');
+create table lp_bc partition of lp for values in ('b', 'c');
+create table lp_g partition of lp for values in ('g');
+create table lp_null partition of lp for values in (null);
+
+select * from lp;
+select * from lp where a > 'a' and a < 'd';
+select * from lp where a > 'a' and a <= 'd';
+select * from lp where a = 'a';
+select * from lp where 'a' = a; /* commuted */
+select * from lp where a is not null;
+select * from lp where a is null;
+select * from lp where a = 'a' or a = 'c';
+select * from lp where a is not null and (a = 'a' or a = 'c');
+select * from lp where a <> 'g';
+select * from lp where a <> 'a' and a <> 'd';
+select * from lp where a not in ('a', 'd');
+
+SELECT plan, calls FROM pg_stat_plans ORDER BY plan COLLATE "C";
+SELECT pg_stat_plans_reset() IS NOT NULL AS t;
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index 7c381949a5..4a5a02c704 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -157,6 +157,7 @@ CREATE EXTENSION <replaceable>extension_name</replaceable>;
&pglogicalinspect;
&pgprewarm;
&pgrowlocks;
+ &pgstatplans;
&pgstatstatements;
&pgstattuple;
&pgsurgery;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 66e6dccd4c..b0afb33ce2 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -146,6 +146,7 @@
<!ENTITY pglogicalinspect SYSTEM "pglogicalinspect.sgml">
<!ENTITY pgprewarm SYSTEM "pgprewarm.sgml">
<!ENTITY pgrowlocks SYSTEM "pgrowlocks.sgml">
+<!ENTITY pgstatplans SYSTEM "pgstatplans.sgml">
<!ENTITY pgstatstatements SYSTEM "pgstatstatements.sgml">
<!ENTITY pgstattuple SYSTEM "pgstattuple.sgml">
<!ENTITY pgsurgery SYSTEM "pgsurgery.sgml">
diff --git a/doc/src/sgml/pgstatplans.sgml b/doc/src/sgml/pgstatplans.sgml
new file mode 100644
index 0000000000..100ce6b3aa
--- /dev/null
+++ b/doc/src/sgml/pgstatplans.sgml
@@ -0,0 +1,413 @@
+<!-- doc/src/sgml/pgstatplans.sgml -->
+
+<sect1 id="pgstatplans" xreflabel="pg_stat_plans">
+ <title>pg_stat_plans — track per-plan call counts, execution times and EXPLAIN texts</title>
+
+ <indexterm zone="pgstatplans">
+ <primary>pg_stat_plans</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pg_stat_plans</filename> module provides a means for
+ tracking per-plan statistics and plan texts of all SQL statements executed by
+ a server.
+ </para>
+
+ <para>
+ The module must be loaded by adding <literal>pg_stat_plans</literal> to
+ <xref linkend="guc-shared-preload-libraries"/> in
+ <filename>postgresql.conf</filename>, because it requires additional shared memory.
+ This means that a server restart is needed to add or remove the module.
+ In addition, query and plan identifier calculation must be enabled in order for the
+ module to be active by setting both <xref linkend="guc-compute-plan-id"/> to <literal>on</literal>
+ and <xref linkend="guc-compute-query-id"/> to <literal>auto</literal> or <literal>on</literal>.
+ </para>
+
+ <para>
+ When <filename>pg_stat_plans</filename> is active, it tracks
+ statistics across all databases of the server. To access and manipulate
+ these statistics, the module provides the <structname>pg_stat_plans</structname>
+ view and the utility functions <function>pg_stat_plans_reset</function> and
+ <function>pg_stat_plans</function>. These are not available globally but
+ can be enabled for a specific database with
+ <command>CREATE EXTENSION pg_stat_plans</command>.
+ </para>
+
+ <sect2 id="pgstatplans-pg-stat-plans">
+ <title>The <structname>pg_stat_plans</structname> View</title>
+
+ <para>
+ The statistics gathered by the module are made available via a
+ view named <structname>pg_stat_plans</structname>. This view
+ contains one row for each distinct combination of database ID, user
+ ID, query ID and plan ID (up to the maximum number of distinct plans
+ that the module can track). The columns of the view are shown in
+ <xref linkend="pgstatplans-columns"/>.
+ </para>
+
+ <table id="pgstatplans-columns">
+ <title><structname>pg_stat_plans</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>userid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of user who executed the statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dbid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of database in which the statement was executed
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>queryid</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Hash code to identify identical normalized queries.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>planid</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Hash code to identify identical plan shapes.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>plan</structfield> <type>text</type>
+ </para>
+ <para>
+ Plan text of a representative plan. This is similar to the output of
+ <literal>EXPLAIN (COSTS OFF)</literal>. Note the plan text will contain constant
+ values of the first plan recorded, but subsequent executions of the
+ same plan hash code (<structfield>planid</structfield>) with different
+ constant values will be tracked under the same entry.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>calls</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the plan was executed
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>total_exec_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Total time spent executing the plan, in milliseconds
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ For security reasons, only superusers and roles with privileges of the
+ <literal>pg_read_all_stats</literal> role are allowed to see the plan text,
+ <structfield>queryid</structfield> and <structfield>planid</structfield>
+ of queries executed by other users. Other users can see the statistics,
+ however, if the view has been installed in their database.
+ </para>
+
+ <para>
+ Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
+ <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>)
+ are combined into a single <structname>pg_stat_plans</structname> entry whenever
+ they have identical plan structures according to an internal hash calculation.
+ Typically, two plans will be considered the same for this purpose if they have
+ the same <literal>EXPLAIN (COSTS OFF)</literal> output and are semantically
+ equivalent except for the values of literal constants appearing in the query plan.
+ </para>
+
+ <para>
+ Note that queries that have not finished executing yet will show in
+ <structname>pg_stat_plans</structname> with their plan text, but without
+ the <structname>calls</structname> field being incremented. This can be
+ used to identify the query plan for a currently running statement by joining
+ <link linkend="monitoring-pg-stat-activity-view">pg_stat_activity</link>
+ with <structname>pg_stat_plans</structname>, see example usage in
+ <xref linkend="pgstatplans-sample-output"/>.
+ </para>
+
+ <para>
+ Consumers of <structname>pg_stat_plans</structname> should use
+ <structfield>planid</structfield> in combination with
+ <structfield>queryid</structfield>, <structfield>dbid</structfield>
+ and <structfield>userid</structfield> as a stable and reliable identifier
+ for each entry, instead of using its plan text. However, it is important
+ to understand that there are only limited guarantees around the stability
+ of the <structfield>planid</structfield> hash value. Since the identifier
+ is derived from the plan tree, its value is a function of, among other
+ things, the internal object identifiers appearing in this representation.
+ This has some counterintuitive implications. For example,
+ <filename>pg_stat_plans</filename> will consider two apparently-identical
+ plans to be distinct, if they reference a table that was dropped
+ and recreated between the creation of the two plans.
+ The hashing process is also sensitive to differences in
+ machine architecture and other facets of the platform.
+ Furthermore, it is not safe to assume that <structfield>planid</structfield>
+ will be stable across major versions of <productname>PostgreSQL</productname>.
+ </para>
+
+ <para>
+ Two servers participating in replication based on physical WAL replay can
+ be expected to have identical <structfield>planid</structfield> values for
+ the same plan. However, logical replication schemes do not promise to
+ keep replicas identical in all relevant details, so
+ <structfield>planid</structfield> will not be a useful identifier for
+ accumulating costs across a set of logical replicas.
+ If in doubt, direct testing is recommended.
+ </para>
+
+ <para>
+ Plan texts are stored in shared memory, and limited in length. To increase
+ the maximum length of stored plan texts you can increase
+ <varname>pg_stat_plans.max_size</varname>. This value can be changed for
+ an individual connection, or set as a server-wide setting.
+ </para>
+ </sect2>
+
+ <sect2 id="pgstatplans-funcs">
+ <title>Functions</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <function>pg_stat_plans_reset() returns void</function>
+ <indexterm>
+ <primary>pg_stat_plans_reset</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pg_stat_plans_reset</function> discards statistics and plan texts
+ gathered so far by <filename>pg_stat_plans</filename>.
+ By default, this function can only be executed by superusers.
+ Access may be granted to others using <command>GRANT</command>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_stat_plans(showplan boolean) returns setof record</function>
+ <indexterm>
+ <primary>pg_stat_plans</primary>
+ <secondary>function</secondary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ The <structname>pg_stat_plans</structname> view is defined in
+ terms of a function also named <function>pg_stat_plans</function>.
+ It is possible for clients to call
+ the <function>pg_stat_plans</function> function directly, and by
+ specifying <literal>showplan := false</literal> have plan texts be
+ omitted (that is, the <literal>OUT</literal> argument that corresponds
+ to the view's <structfield>plan</structfield> column will return nulls). This
+ feature is intended to support external tools that might wish to avoid
+ the overhead of repeatedly retrieving plan texts of indeterminate
+ length. Such tools can instead cache the first plan text observed
+ for each entry themselves, since that is
+ all <filename>pg_stat_plans</filename> itself does, and then retrieve
+ plan texts only as needed.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2 id="pgstatplans-config-params">
+ <title>Configuration Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <varname>pg_stat_plans.max</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>pg_stat_plans.max</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <varname>pg_stat_plans.max</varname> is the maximum number of
+ plans tracked by the module (i.e., the maximum number of rows
+ in the <structname>pg_stat_plans</structname> view). If more distinct
+ plans than that are observed, information about the least-executed
+ plans is discarded. The default value is 5000.
+ Only superusers can change this setting. Changing the setting requires
+ a reload of the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <varname>pg_stat_plans.max_size</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>pg_stat_plans.max_size</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <varname>pg_stat_plans.max_size</varname> is the maximum length of
+ each plan text tracked by the module in bytes. Longer plan texts will be truncated.
+ The default value is 2048 (2kB).
+ Only superusers can change this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <varname>pg_stat_plans.track</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>pg_stat_plans.track</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <varname>pg_stat_plans.track</varname> controls which plans
+ are counted by the module.
+ Specify <literal>top</literal> to track plans by top-level statements (those issued
+ directly by clients), <literal>all</literal> to also track nested statements
+ (such as statements invoked within functions), or <literal>none</literal> to
+ disable plan statistics collection.
+ The default value is <literal>top</literal>.
+ Only superusers can change this setting.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ The module requires additional shared memory proportional to
+ <varname>pg_stat_plans.max</varname> for statistics, as well as
+ <varname>pg_stat_plans.max</varname> multiplied by
+ <varname>pg_stat_plans.max_size</varname> for plan texts. Note that this
+ memory is only consumed when entries are created, and not if
+ <varname>pg_stat_plans.track</varname> is set to <literal>none</literal>.
+ </para>
+
+ <para>
+ These parameters must be set in <filename>postgresql.conf</filename>.
+ Typical usage might be:
+
+<programlisting>
+# postgresql.conf
+shared_preload_libraries = 'pg_stat_plans'
+
+compute_query_id = on
+compute_plan_id = on
+pg_stat_plans.max = 10000
+pg_stat_plans.max_size = 4096
+pg_stat_plans.track = all
+</programlisting>
+ </para>
+ </sect2>
+
+ <sect2 id="pgstatplans-sample-output">
+ <title>Sample Output</title>
+
+<screen>
+bench=# SELECT pg_stat_plans_reset();
+
+$ pgbench -i bench
+$ pgbench -c10 -t300 bench
+
+bench=# \x
+bench=# SELECT plan, calls, total_exec_time
+ FROM pg_stat_plans ORDER BY total_exec_time DESC LIMIT 5;
+-[ RECORD 1 ]---+-----------------------------------------------------------------
+plan | Update on pgbench_tellers +
+ | -> Seq Scan on pgbench_tellers +
+ | Filter: (tid = 5)
+calls | 3000
+total_exec_time | 642.8880919999993
+-[ RECORD 2 ]---+-----------------------------------------------------------------
+plan | Update on pgbench_branches +
+ | -> Seq Scan on pgbench_branches +
+ | Filter: (bid = 1)
+calls | 1813
+total_exec_time | 476.64152700000005
+-[ RECORD 3 ]---+-----------------------------------------------------------------
+plan | Update on pgbench_branches +
+ | -> Index Scan using pgbench_branches_pkey on pgbench_branches+
+ | Index Cond: (bid = 1)
+calls | 1187
+total_exec_time | 326.1257549999999
+-[ RECORD 4 ]---+-----------------------------------------------------------------
+plan | Update on pgbench_accounts +
+ | -> Index Scan using pgbench_accounts_pkey on pgbench_accounts+
+ | Index Cond: (aid = 48793)
+calls | 3000
+total_exec_time | 21.664690000000093
+-[ RECORD 5 ]---+-----------------------------------------------------------------
+plan | Insert on pgbench_history +
+ | -> Result
+calls | 3000
+total_exec_time | 4.365250999999957
+
+session 1:
+
+bench# SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts;
+
+session 2:
+
+bench=# SELECT query, plan FROM pg_stat_activity
+ JOIN pg_stat_plans ON (usesysid = userid AND datid = dbid AND query_id = queryid AND plan_id = planid)
+ WHERE query LIKE 'SELECT pg_sleep%';
+ query | plan
+-------------------------------------------------------+------------------------------------
+ SELECT pg_sleep(100), COUNT(*) FROM pgbench_accounts; | Aggregate +
+ | -> Seq Scan on pgbench_accounts
+(1 row)
+
+</screen>
+ </sect2>
+
+ <sect2 id="pgstatplans-authors">
+ <title>Authors</title>
+
+ <para>
+ Lukas Fittl <email>[email protected]</email>.
+ </para>
+ </sect2>
+
+</sect1>
--
2.47.1
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PATCH] Optionally record Plan IDs to track plan changes for a query
2025-01-02 20:46 [PATCH] Optionally record Plan IDs to track plan changes for a query Lukas Fittl <[email protected]>
@ 2025-01-24 09:23 ` Andrei Lepikhov <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Andrei Lepikhov @ 2025-01-24 09:23 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; +Cc: Marko M <[email protected]>; Sami Imseih <[email protected]>
On 1/3/25 03:46, Lukas Fittl wrote:
> My overall perspective is that (1) is best done in-core to keep overhead
> low, whilst (2) could be done outside of core (or merged with a future
> pg_stat_statements) and is included here mainly for illustration purposes.
Thank you for the patch and your attention to this issue!
I am pleased with the export of the jumbling functions and their
generalisation.
I may not be close to the task monitoring area, but I utilise queryId
and other tools to differ plan nodes inside extensions. Initially, like
queryId serves as a class identifier for queries, plan_id identifies a
class of nodes, not a single node. In the implementation provided here,
nodes with the same hash can represent different subtrees. For example,
JOIN(A, JOIN(B,C)) and JOIN(JOIN(B,C),A) may have the same ID.
Moreover, I wonder if this version of plan_id reacts to the join level
change. It appears that only a change of the join clause alters the
plan_id hash value, which means you would end up with a single hash for
very different plan nodes. Is that acceptable? To address this, we
should consider the hashes of the left and right subtrees and the hashes
of each subplan (especially in the case of Append).
Overall, similar to discussions on queryId, various extensions may want
different logic for generating plan_id (more or less unique guarantees,
for example). Hence, it would be beneficial to separate this logic and
allow extensions to provide different plan_ids. IMO, What we need is a
'List *ext' field in each of the Plan, Path, PlanStmt, and Query
structures. Such 'ext' field may contain different stuff that extensions
want to push without interference between them - specific plan_id as an
example.
Additionally, we could bridge the gap between the cloud of paths and the
plan by adding a hook at the end of the create_plan_recurse routine.
This may facilitate the transfer of information regarding optimiser
decisions that could be influenced by an extension into the plan.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-01-24 09:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-02 20:46 [PATCH] Optionally record Plan IDs to track plan changes for a query Lukas Fittl <[email protected]>
2025-01-24 09:23 ` Andrei Lepikhov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox