0004-Add-pg_stat_plans-contrib-extension.patch
application/octet-stream
Filename: 0004-Add-pg_stat_plans-contrib-extension.patch
Type: application/octet-stream
Part: 3
From 9840edbbfb22e4c6ffbbf6a5eb80aa863a95edf6 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <lukas@fittl.com>
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>lukas@fittl.com</email>.
+ </para>
+ </sect2>
+
+</sect1>
--
2.47.1