From 8d5bdca84bf3aaa2f341ada9f024a66a2c6ed8fc Mon Sep 17 00:00:00 2001 From: Sami Imseih 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 @@ max_exec_time) + + + + generic_plan_calls bigint + + + Total number of non-utility statements executed using a generic plan + + + + + + custom_plan_calls bigint + + + Total number of non-utility statements executed using a custom plan + + 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