public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: track generic and custom plans in pg_stat_statements
Date: Wed, 5 Mar 2025 12:45:30 -0600
Message-ID: <CAA5RZ0uFw8Y9GCFvafhC=OA8NnMqVZyzXPfv_EePOt+iv1T-qQ@mail.gmail.com> (raw)
Hi,
Currently, there is little visibility for queries that are being executed
using generic or custom plans. There is pg_prepared_statements which
show generic_plans and custom_plans as of d05b172a760, but this
information is backend local and not very useful to a DBA that wishes
to track this information cumulatively and over time. [0] had intentions
to add these counters to pg_stat_statements, but was withdrawn due
to timing with the commitfest at the time [0] and never picked back up again.
I think it's useful to add these counters.
Therefore, the attached patch adds two new columns to pg_stat_statements:
"generic_plan_calls" and "custom_plan_calls". These columns track the
number of executions performed using a generic or custom plan.
Only non-utility statements are counted, as utility statements with an
optimizable parameterized query (i.e. CTAS) cannot be called with PREPARE.
Additionally, when such statements are repeatedly executed using an extended
protocol prepared statement, pg_stat_statements may not handle them properly,
since queryId is set to 0 during pgss_ProcessUtility.
To avoid introducing two additional counters in CachedPlan, the existing
boolean is_reusable—which determines whether a generic plan is reused to
manage lock requirements—has been repurposed as an enum. This enum now
tracks different plan states, including "generic reused", "generic first time"
and "custom". pg_stat_statements uses these states to differentiate between
generic and custom plans for tracking purposes. ( I felt this was better than
having to carry 2 extra booleans in CachedPlan for this purpose, but that will
be the alternative approach ).
Not included in this patch and maybe for follow-up work, is this information
can be added to EXPLAIN output and perhaps pg_stat_database. Maybe that's
a good idea also?
This patch bumps the version pf pg_stat_statements.
--
Sami Imseih
Amazon Web Services (AWS)
[0] https://www.postgresql.org/message-id/add1e591fbe8874107e75d04328859ec%40oss.nttdata.com
Attachments:
[application/octet-stream] v1-0001-add-plan_cache-counters-to-pg_stat_statements.patch (33.5K, ../CAA5RZ0uFw8Y9GCFvafhC=OA8NnMqVZyzXPfv_EePOt+iv1T-qQ@mail.gmail.com/2-v1-0001-add-plan_cache-counters-to-pg_stat_statements.patch)
download | inline diff:
From 8d5bdca84bf3aaa2f341ada9f024a66a2c6ed8fc Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 4 Mar 2025 08:54:56 -0600
Subject: [PATCH v1 1/1] add plan_cache counters to pg_stat_statements.
---
contrib/pg_stat_statements/Makefile | 3 +-
.../expected/plan_cache.out | 500 ++++++++++++++++++
contrib/pg_stat_statements/meson.build | 4 +-
.../pg_stat_statements--1.12--1.13.sql | 78 +++
.../pg_stat_statements/pg_stat_statements.c | 51 +-
.../pg_stat_statements.control | 2 +-
contrib/pg_stat_statements/sql/plan_cache.sql | 155 ++++++
doc/src/sgml/pgstatstatements.sgml | 18 +
src/backend/utils/cache/plancache.c | 10 +-
src/include/utils/plancache.h | 12 +-
10 files changed, 818 insertions(+), 15 deletions(-)
create mode 100644 contrib/pg_stat_statements/expected/plan_cache.out
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
create mode 100644 contrib/pg_stat_statements/sql/plan_cache.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 241c02587b..bb81256548 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -7,6 +7,7 @@ OBJS = \
EXTENSION = pg_stat_statements
DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.12--1.13.sql \
pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.10--1.11.sql \
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
@@ -20,7 +21,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
REGRESS = select dml cursors utility level_tracking planning \
user_activity wal entry_timestamp privileges extended \
- parallel cleanup oldextversions
+ parallel plan_cache cleanup oldextversions
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/plan_cache.out b/contrib/pg_stat_statements/expected/plan_cache.out
new file mode 100644
index 0000000000..24b6e61710
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/plan_cache.out
@@ -0,0 +1,500 @@
+--
+-- Information related to plan cache
+--
+-- setup test
+CREATE TABLE plan_cache_tab (x int, y int);
+CREATE OR REPLACE FUNCTION plan_cache_func(x_in int) RETURNS int AS $$
+ DECLARE
+ y_out int;
+ BEGIN
+ select y into y_out from plan_cache_tab where x = x_in;
+ return y_out;
+ END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE plan_cache_proc(x_in int) AS $$
+ DECLARE
+ y_out int;
+ BEGIN
+ select y into y_out from plan_cache_tab where x = x_in;
+ END;
+$$ LANGUAGE plpgsql;
+-- plan cache counters for extended query protocol and sql prepared statements
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+select y from plan_cache_tab where x = $1 \parse p1
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+\bind_named p1 1
+;
+ y
+---
+(0 rows)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | query
+-------+--------------------+-------------------+----------------------------------------------------
+ 1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 1 | 5 | select y from plan_cache_tab where x = $1
+(2 rows)
+
+DEALLOCATE p1;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | query
+-------+--------------------+-------------------+----------------------------------------------------
+ 1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 1 | 5 | select y from plan_cache_tab where x = $1
+(2 rows)
+
+DEALLOCATE p1;
+-- plan cache counters with plan invalidation.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+CREATE INDEX ON plan_cache_tab (x);
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+EXECUTE p1(1);
+ y
+---
+(0 rows)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | query
+-------+--------------------+-------------------+----------------------------------------------------
+ 1 | 0 | 0 | CREATE INDEX ON plan_cache_tab (x)
+ 1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 9 | 4 | 5 | select y from plan_cache_tab where x = $1
+(3 rows)
+
+DEALLOCATE p1;
+-- plan cache counters for functions and procedures
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+
+(1 row)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | toplevel | query
+-------+--------------------+-------------------+----------+--------------------------------------------------------
+ 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 0 | 0 | t | SELECT plan_cache_func($1)
+ 6 | 1 | 5 | f | select y from plan_cache_tab where x = x_in
+(3 rows)
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | toplevel | query
+-------+--------------------+-------------------+----------+--------------------------------------------------------
+ 6 | 0 | 0 | t | CALL plan_cache_proc($1)
+ 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 1 | 5 | f | select y from plan_cache_tab where x = x_in
+(3 rows)
+
+-- plan cache counters are not tracked for utility statements
+DROP TABLE plan_cache_tab;
+CREATE TABLE plan_cache_tab AS SELECT $1::int AS x, $2::int AS y \parse p1
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+\bind_named p1 1 2
+;
+DROP TABLE plan_cache_tab;
+\bind_named p1 1 2
+;
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | toplevel | query
+-------+--------------------+-------------------+----------+------------------------------------------------------------------
+ 2 | 0 | 0 | t | CREATE TABLE plan_cache_tab AS SELECT $1::int AS x, $2::int AS y
+ 2 | 0 | 0 | f | CREATE TABLE plan_cache_tab AS SELECT $1::int AS x, $2::int AS y
+ 1 | 0 | 0 | t | DROP TABLE plan_cache_tab
+ 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(4 rows)
+
+DEALLOCATE p1;
+-- plan cache mode generic
+SET plan_cache_mode = force_generic_plan;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | query
+-------+--------------------+-------------------+----------------------------------------------------
+ 1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 6 | 0 | select y from plan_cache_tab where x = $1
+(2 rows)
+
+DEALLOCATE p1;
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | toplevel | query
+-------+--------------------+-------------------+----------+--------------------------------------------------------
+ 6 | 0 | 0 | t | CALL plan_cache_proc($1)
+ 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 0 | 0 | t | SELECT plan_cache_func($1)
+ 12 | 12 | 0 | f | select y from plan_cache_tab where x = x_in
+(4 rows)
+
+RESET pg_stat_statements.track;
+-- custom plan mode
+SET plan_cache_mode = force_custom_plan;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+EXECUTE p1(1);
+ y
+---
+ 2
+(1 row)
+
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | query
+-------+--------------------+-------------------+----------------------------------------------------
+ 1 | 0 | 0 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 0 | 6 | select y from plan_cache_tab where x = $1
+(2 rows)
+
+DEALLOCATE p1;
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+SELECT plan_cache_func(1);
+ plan_cache_func
+-----------------
+ 2
+(1 row)
+
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+ calls | generic_plan_calls | custom_plan_calls | toplevel | query
+-------+--------------------+-------------------+----------+--------------------------------------------------------
+ 6 | 0 | 0 | t | CALL plan_cache_proc($1)
+ 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+ 6 | 0 | 0 | t | SELECT plan_cache_func($1)
+ 12 | 0 | 12 | f | select y from plan_cache_tab where x = x_in
+(4 rows)
+
+RESET pg_stat_statements.track;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 4446af58c5..95f175848f 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements
install_data(
'pg_stat_statements.control',
'pg_stat_statements--1.4.sql',
+ 'pg_stat_statements--1.12--1.13.sql',
'pg_stat_statements--1.11--1.12.sql',
'pg_stat_statements--1.10--1.11.sql',
'pg_stat_statements--1.9--1.10.sql',
@@ -54,8 +55,9 @@ tests += {
'privileges',
'extended',
'parallel',
+ 'plan_cache'
'cleanup',
- 'oldextversions',
+ 'oldextversions'
],
'regress_args': ['--temp-config', files('pg_stat_statements.conf')],
# Disabled because these tests require
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
new file mode 100644
index 0000000000..67f2f7dbca
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql
@@ -0,0 +1,78 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.12--1.13.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.13'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT toplevel bool,
+ OUT queryid bigint,
+ OUT query text,
+ OUT plans int8,
+ OUT total_plan_time float8,
+ OUT min_plan_time float8,
+ OUT max_plan_time float8,
+ OUT mean_plan_time float8,
+ OUT stddev_plan_time float8,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT min_exec_time float8,
+ OUT max_exec_time float8,
+ OUT mean_exec_time float8,
+ OUT stddev_exec_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT shared_blk_read_time float8,
+ OUT shared_blk_write_time float8,
+ OUT local_blk_read_time float8,
+ OUT local_blk_write_time float8,
+ OUT temp_blk_read_time float8,
+ OUT temp_blk_write_time float8,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT wal_buffers_full int8,
+ OUT jit_functions int8,
+ OUT jit_generation_time float8,
+ OUT jit_inlining_count int8,
+ OUT jit_inlining_time float8,
+ OUT jit_optimization_count int8,
+ OUT jit_optimization_time float8,
+ OUT jit_emission_count int8,
+ OUT jit_emission_time float8,
+ OUT jit_deform_count int8,
+ OUT jit_deform_time float8,
+ OUT parallel_workers_to_launch int8,
+ OUT parallel_workers_launched int8,
+ OUT stats_since timestamp with time zone,
+ OUT minmax_stats_since timestamp with time zone,
+ OUT generic_plan_calls int8,
+ OUT custom_plan_calls int8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_13'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index b245d04097..e5ff32ab58 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -69,6 +69,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/plancache.h"
#include "utils/timestamp.h"
PG_MODULE_MAGIC;
@@ -111,6 +112,7 @@ typedef enum pgssVersion
PGSS_V1_10,
PGSS_V1_11,
PGSS_V1_12,
+ PGSS_V1_13,
} pgssVersion;
typedef enum pgssStoreKind
@@ -207,6 +209,8 @@ typedef struct Counters
* to be launched */
int64 parallel_workers_launched; /* # of parallel workers actually
* launched */
+ int64 generic_plan_calls; /* number of calls using a generic plan */
+ int64 custom_plan_calls; /* number of calls using a custom plan */
} Counters;
/*
@@ -321,6 +325,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_11);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_12);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_13);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -353,7 +358,8 @@ static void pgss_store(const char *query, uint64 queryId,
const struct JitInstrumentation *jitusage,
JumbleState *jstate,
int parallel_workers_to_launch,
- int parallel_workers_launched);
+ int parallel_workers_launched,
+ CachedPlan *cplan);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
bool showtext);
@@ -875,7 +881,8 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
NULL,
jstate,
0,
- 0);
+ 0,
+ NULL);
}
/*
@@ -955,7 +962,8 @@ pgss_planner(Query *parse,
NULL,
NULL,
0,
- 0);
+ 0,
+ NULL);
}
else
{
@@ -1097,7 +1105,8 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
queryDesc->estate->es_jit ? &queryDesc->estate->es_jit->instr : NULL,
NULL,
queryDesc->estate->es_parallel_workers_to_launch,
- queryDesc->estate->es_parallel_workers_launched);
+ queryDesc->estate->es_parallel_workers_launched,
+ queryDesc->cplan);
}
if (prev_ExecutorEnd)
@@ -1230,7 +1239,8 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
NULL,
NULL,
0,
- 0);
+ 0,
+ NULL);
}
else
{
@@ -1293,7 +1303,8 @@ pgss_store(const char *query, uint64 queryId,
const struct JitInstrumentation *jitusage,
JumbleState *jstate,
int parallel_workers_to_launch,
- int parallel_workers_launched)
+ int parallel_workers_launched,
+ CachedPlan *cplan)
{
pgssHashKey key;
pgssEntry *entry;
@@ -1501,6 +1512,11 @@ pgss_store(const char *query, uint64 queryId,
entry->counters.parallel_workers_to_launch += parallel_workers_to_launch;
entry->counters.parallel_workers_launched += parallel_workers_launched;
+ if (cplan && cplan->status > PLAN_CACHE_STATUS_CUSTOM_PLAN)
+ entry->counters.generic_plan_calls++;
+ if (cplan && cplan->status == PLAN_CACHE_STATUS_CUSTOM_PLAN)
+ entry->counters.custom_plan_calls++;
+
SpinLockRelease(&entry->mutex);
}
@@ -1568,7 +1584,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_10 43
#define PG_STAT_STATEMENTS_COLS_V1_11 49
#define PG_STAT_STATEMENTS_COLS_V1_12 52
-#define PG_STAT_STATEMENTS_COLS 52 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_13 54
+#define PG_STAT_STATEMENTS_COLS 54 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1580,6 +1597,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_13(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_13, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_12(PG_FUNCTION_ARGS)
{
@@ -1738,6 +1765,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_12)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_13:
+ if (api_version != PGSS_V1_13)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1995,6 +2026,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
values[i++] = TimestampTzGetDatum(stats_since);
values[i++] = TimestampTzGetDatum(minmax_stats_since);
}
+ if (api_version >= PGSS_V1_13)
+ {
+ values[i++] = Int64GetDatumFast(tmp.generic_plan_calls);
+ values[i++] = Int64GetDatumFast(tmp.custom_plan_calls);
+ }
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
@@ -2005,6 +2041,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 :
api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 :
+ api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index d45ebc12e3..2eee0ceffa 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track planning and execution statistics of all SQL statements executed'
-default_version = '1.12'
+default_version = '1.13'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/plan_cache.sql b/contrib/pg_stat_statements/sql/plan_cache.sql
new file mode 100644
index 0000000000..69ed75a278
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/plan_cache.sql
@@ -0,0 +1,155 @@
+--
+-- Information related to plan cache
+--
+
+-- setup test
+CREATE TABLE plan_cache_tab (x int, y int);
+CREATE OR REPLACE FUNCTION plan_cache_func(x_in int) RETURNS int AS $$
+ DECLARE
+ y_out int;
+ BEGIN
+ select y into y_out from plan_cache_tab where x = x_in;
+ return y_out;
+ END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE plan_cache_proc(x_in int) AS $$
+ DECLARE
+ y_out int;
+ BEGIN
+ select y into y_out from plan_cache_tab where x = x_in;
+ END;
+$$ LANGUAGE plpgsql;
+
+-- plan cache counters for extended query protocol and sql prepared statements
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+select y from plan_cache_tab where x = $1 \parse p1
+\bind_named p1 1
+;
+\bind_named p1 1
+;
+\bind_named p1 1
+;
+\bind_named p1 1
+;
+\bind_named p1 1
+;
+\bind_named p1 1
+;
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+
+-- plan cache counters with plan invalidation.
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+CREATE INDEX ON plan_cache_tab (x);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+
+-- plan cache counters for functions and procedures
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- plan cache counters are not tracked for utility statements
+DROP TABLE plan_cache_tab;
+CREATE TABLE plan_cache_tab AS SELECT $1::int AS x, $2::int AS y \parse p1
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+\bind_named p1 1 2
+;
+DROP TABLE plan_cache_tab;
+\bind_named p1 1 2
+;
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+
+-- plan cache mode generic
+SET plan_cache_mode = force_generic_plan;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+RESET pg_stat_statements.track;
+
+-- custom plan mode
+SET plan_cache_mode = force_custom_plan;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+PREPARE P1(int) AS select y from plan_cache_tab where x = $1;
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+EXECUTE p1(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+DEALLOCATE p1;
+SET pg_stat_statements.track = "all";
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+SELECT plan_cache_func(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+CALL plan_cache_proc(1);
+SELECT calls, generic_plan_calls, custom_plan_calls, toplevel, query FROM pg_stat_statements ORDER BY query COLLATE "C";
+RESET pg_stat_statements.track;
\ No newline at end of file
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index e2ac1c2d50..8a2379e22c 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -575,6 +575,24 @@
<structfield>max_exec_time</structfield>)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>generic_plan_calls</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of non-utility statements executed using a generic plan
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>custom_plan_calls</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of non-utility statements executed using a custom plan
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 6c2979d5c8..510e2d8286 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -1056,7 +1056,7 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
plan->stmt_context = stmt_context;
plan->is_oneshot = plansource->is_oneshot;
plan->is_saved = false;
- plan->is_reused = false;
+ plan->status = PLAN_CACHE_STATUS_UNKNOWN;
plan->is_valid = true;
/* assign generation number to new plan */
@@ -1294,7 +1294,8 @@ cached_plan_cost(CachedPlan *plan, bool include_planner)
* locks are acquired. In such cases, CheckCachedPlan() does not take locks
* on relations subject to initial runtime pruning; instead, these locks are
* deferred until execution startup, when ExecDoInitialPruning() performs
- * initial pruning. The plan's "is_reused" flag is set to indicate that
+ * initial pruning. The plan's "status" flag is set to
+ * PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE to indicate that
* CachedPlanRequiresLocking() should return true when called by
* ExecDoInitialPruning().
*
@@ -1335,7 +1336,7 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
plan = plansource->gplan;
Assert(plan->magic == CACHEDPLAN_MAGIC);
/* Reusing the existing plan, so not all locks may be acquired. */
- plan->is_reused = true;
+ plan->status = PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE;
}
else
{
@@ -1379,6 +1380,8 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
* BuildCachedPlan to do that by passing NIL.
*/
qlist = NIL;
+
+ plan->status = PLAN_CACHE_STATUS_GENERIC_PLAN_BUILD;
}
}
@@ -1390,6 +1393,7 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
plansource->total_custom_cost += cached_plan_cost(plan, true);
plansource->num_custom_plans++;
+ plan->status = PLAN_CACHE_STATUS_CUSTOM_PLAN;
}
else
{
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index f1fc770733..72b756864b 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -36,6 +36,14 @@ typedef enum
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN,
} PlanCacheMode;
+typedef enum
+{
+ PLAN_CACHE_STATUS_UNKNOWN = 0,
+ PLAN_CACHE_STATUS_CUSTOM_PLAN,
+ PLAN_CACHE_STATUS_GENERIC_PLAN_BUILD,
+ PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE
+} PlanCacheStatus;
+
/* GUC parameter */
extern PGDLLIMPORT int plan_cache_mode;
@@ -153,7 +161,7 @@ typedef struct CachedPlan
List *stmt_list; /* list of PlannedStmts */
bool is_oneshot; /* is it a "oneshot" plan? */
bool is_saved; /* is CachedPlan in a long-lived context? */
- bool is_reused; /* is it a reused generic plan? */
+ PlanCacheStatus status; /* is it a reused generic plan? */
bool is_valid; /* is the stmt_list currently valid? */
Oid planRoleId; /* Role ID the plan was created for */
bool dependsOnRole; /* is plan specific to that role? */
@@ -257,7 +265,7 @@ extern void FreeCachedExpression(CachedExpression *cexpr);
static inline bool
CachedPlanRequiresLocking(CachedPlan *cplan)
{
- return !cplan->is_oneshot && cplan->is_reused;
+ return !cplan->is_oneshot && (cplan->status == PLAN_CACHE_STATUS_GENERIC_PLAN_REUSE);
}
/*
--
2.47.1
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: track generic and custom plans in pg_stat_statements
In-Reply-To: <CAA5RZ0uFw8Y9GCFvafhC=OA8NnMqVZyzXPfv_EePOt+iv1T-qQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox