On 2021-03-05 17:47, Fujii Masao wrote:
Thanks for your comments!
I just tried this feature. When I set plan_cache_mode to
force_generic_plan
and executed the following queries, I found that
pg_stat_statements.generic_calls
and pg_prepared_statements.generic_plans were not the same.
Is this behavior expected? I was thinking that they are basically the
same.
It's not expected behavior, fixed.
DEALLOCATE ALL;
SELECT pg_stat_statements_reset();
PREPARE hoge AS SELECT * FROM pgbench_accounts WHERE aid = $1;
EXECUTE hoge(1);
EXECUTE hoge(1);
EXECUTE hoge(1);
SELECT generic_plans, statement FROM pg_prepared_statements WHERE
statement LIKE '%hoge%';
generic_plans | statement
---------------+----------------------------------------------------------------
3 | PREPARE hoge AS SELECT * FROM pgbench_accounts WHERE
aid = $1;
SELECT calls, generic_calls, query FROM pg_stat_statements WHERE query
LIKE '%hoge%';
calls | generic_calls | query
-------+---------------+---------------------------------------------------------------
3 | 2 | PREPARE hoge AS SELECT * FROM
pgbench_accounts WHERE aid = $1
When I executed the prepared statements via EXPLAIN ANALYZE, I found
pg_stat_statements.generic_calls was not incremented. Is this behavior
expected?
Or we should count generic_calls even when executing the queries via
ProcessUtility()?
I think prepared statements via EXPLAIN ANALYZE also should be counted
for consistency with pg_prepared_statements.
Since ActivePortal did not keep the plan type in the
ProcessUtility_hook,
I moved the global variables 'is_plan_type_generic' and
'is_prev_plan_type_generic' from pg_stat_statements to plancache.c.
DEALLOCATE ALL;
SELECT pg_stat_statements_reset();
PREPARE hoge AS SELECT * FROM pgbench_accounts WHERE aid = $1;
EXPLAIN ANALYZE EXECUTE hoge(1);
EXPLAIN ANALYZE EXECUTE hoge(1);
EXPLAIN ANALYZE EXECUTE hoge(1);
SELECT generic_plans, statement FROM pg_prepared_statements WHERE
statement LIKE '%hoge%';
generic_plans | statement
---------------+----------------------------------------------------------------
3 | PREPARE hoge AS SELECT * FROM pgbench_accounts WHERE
aid = $1;
SELECT calls, generic_calls, query FROM pg_stat_statements WHERE query
LIKE '%hoge%';
calls | generic_calls | query
-------+---------------+---------------------------------------------------------------
3 | 0 | PREPARE hoge AS SELECT * FROM
pgbench_accounts WHERE aid = $1
3 | 0 | EXPLAIN ANALYZE EXECUTE hoge(1)
Regards,
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 16158525ca..887c4b2be8 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -251,6 +251,72 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
UPDATE pgss_test SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
(7 rows)
+--
+-- Track the number of generic plan
+--
+CREATE TABLE pgss_test (i int, j int, k int);
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SET plan_cache_mode TO force_generic_plan;
+SET pg_stat_statements.track_utility = TRUE;
+PREPARE pgss_p1 AS SELECT i FROM pgss_test WHERE i = $1;
+EXECUTE pgss_p1(1);
+ i
+---
+(0 rows)
+
+-- EXPLAIN ANALZE should be recorded
+PREPARE pgss_p2 AS SELECT j FROM pgss_test WHERE j = $1;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE pgss_p2(1);
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on pgss_test (actual rows=0 loops=1)
+ Filter: (j = $1)
+(2 rows)
+
+-- Nested Portal
+PREPARE pgss_p3 AS SELECT k FROM pgss_test WHERE k = $1;
+BEGIN;
+DECLARE pgss_c1 CURSOR FOR SELECT name FROM pg_prepared_statements;
+FETCH IN pgss_c1;
+ name
+---------
+ pgss_p2
+(1 row)
+
+EXECUTE pgss_p3(1);
+ k
+---
+(0 rows)
+
+FETCH IN pgss_c1;
+ name
+---------
+ pgss_p1
+(1 row)
+
+COMMIT;
+SELECT calls, generic_calls, query FROM pg_stat_statements;
+ calls | generic_calls | query
+-------+---------------+--------------------------------------------------------------------------
+ 1 | 0 | DECLARE pgss_c1 CURSOR FOR SELECT name FROM pg_prepared_statements
+ 0 | 0 | SELECT calls, generic_calls, query FROM pg_stat_statements
+ 1 | 1 | PREPARE pgss_p1 AS SELECT i FROM pgss_test WHERE i = $1
+ 2 | 0 | FETCH IN pgss_c1
+ 1 | 0 | BEGIN
+ 1 | 0 | SELECT pg_stat_statements_reset()
+ 1 | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE pgss_p2(1)
+ 1 | 0 | COMMIT
+ 1 | 1 | PREPARE pgss_p3 AS SELECT k FROM pgss_test WHERE k = $1
+(9 rows)
+
+SET pg_stat_statements.track_utility = FALSE;
+DEALLOCATE ALL;
+DROP TABLE pgss_test;
--
-- pg_stat_statements.track = none
--
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
index 0f63f08f7e..7fdef315ae 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql
@@ -44,7 +44,8 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
OUT blk_write_time float8,
OUT wal_records int8,
OUT wal_fpi int8,
- OUT wal_bytes numeric
+ OUT wal_bytes numeric,
+ OUT generic_calls int8
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'pg_stat_statements_1_8'
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 62cccbfa44..b14919c989 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -81,6 +81,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/plancache.h"
#include "utils/timestamp.h"
PG_MODULE_MAGIC;
@@ -192,6 +193,7 @@ typedef struct Counters
int64 wal_records; /* # of WAL records generated */
int64 wal_fpi; /* # of WAL full page images generated */
uint64 wal_bytes; /* total amount of WAL generated in bytes */
+ int64 generic_calls; /* # of times generic plans executed */
} Counters;
/*
@@ -1446,6 +1448,10 @@ pgss_store(const char *query, uint64 queryId,
if (e->counters.max_time[kind] < total_time)
e->counters.max_time[kind] = total_time;
}
+
+ if (kind == PGSS_EXEC && is_plan_type_generic)
+ e->counters.generic_calls += 1;
+
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
e->counters.shared_blks_read += bufusage->shared_blks_read;
@@ -1510,8 +1516,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_1 18
#define PG_STAT_STATEMENTS_COLS_V1_2 19
#define PG_STAT_STATEMENTS_COLS_V1_3 23
-#define PG_STAT_STATEMENTS_COLS_V1_8 32
-#define PG_STAT_STATEMENTS_COLS 32 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_8 33
+#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1863,6 +1869,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
ObjectIdGetDatum(0),
Int32GetDatum(-1));
values[i++] = wal_bytes;
+ values[i++] = Int64GetDatumFast(tmp.generic_calls);
}
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 6f58d9d0f6..06f34d8450 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -125,6 +125,39 @@ wal_records > 0 as wal_records_generated,
wal_records = rows as wal_records_as_rows
FROM pg_stat_statements ORDER BY query COLLATE "C";
+--
+-- Track the number of generic plan
+--
+CREATE TABLE pgss_test (i int, j int, k int);
+SELECT pg_stat_statements_reset();
+SET plan_cache_mode TO force_generic_plan;
+SET pg_stat_statements.track_utility = TRUE;
+
+PREPARE pgss_p1 AS SELECT i FROM pgss_test WHERE i = $1;
+EXECUTE pgss_p1(1);
+
+-- EXPLAIN ANALZE should be recorded
+PREPARE pgss_p2 AS SELECT j FROM pgss_test WHERE j = $1;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) EXECUTE pgss_p2(1);
+
+-- Nested Portal
+PREPARE pgss_p3 AS SELECT k FROM pgss_test WHERE k = $1;
+
+BEGIN;
+DECLARE pgss_c1 CURSOR FOR SELECT name FROM pg_prepared_statements;
+
+FETCH IN pgss_c1;
+EXECUTE pgss_p3(1);
+FETCH IN pgss_c1;
+
+COMMIT;
+
+SELECT calls, generic_calls, query FROM pg_stat_statements;
+
+SET pg_stat_statements.track_utility = FALSE;
+DEALLOCATE ALL;
+DROP TABLE pgss_test;
+
--
-- pg_stat_statements.track = none
--
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 464bf0e5ae..8c5e304882 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -363,6 +363,14 @@
Total amount of WAL generated by the statement in bytes
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>generic_calls</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the statement was executed as generic plan
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 0648dd82ba..159f07d536 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -61,6 +61,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/partcache.h"
+#include "utils/plancache.h"
#include "utils/rls.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index c1f4128445..68535a71bc 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -83,6 +83,10 @@
((plansource)->raw_parse_tree && \
IsA((plansource)->raw_parse_tree->stmt, TransactionStmt))
+/* Set whether the current and previous plan is generic or not */
+bool is_plan_type_generic = false;
+bool is_prev_plan_type_generic = false;
+
/*
* This is the head of the backend's list of "saved" CachedPlanSources (i.e.,
* those that are in long-lived storage and are examined for sinval events).
@@ -1219,10 +1223,15 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
plansource->total_custom_cost += cached_plan_cost(plan, true);
plansource->num_custom_plans++;
+ is_prev_plan_type_generic = is_plan_type_generic;
+ is_plan_type_generic = false;
+
}
else
{
plansource->num_generic_plans++;
+ is_prev_plan_type_generic = is_plan_type_generic;
+ is_plan_type_generic = true;
}
Assert(plan != NULL);
diff --git a/src/backend/utils/mmgr/portalmem.c b/src/backend/utils/mmgr/portalmem.c
index 66e3181815..040f1ca5d3 100644
--- a/src/backend/utils/mmgr/portalmem.c
+++ b/src/backend/utils/mmgr/portalmem.c
@@ -592,6 +592,9 @@ PortalDrop(Portal portal, bool isTopCommit)
/* release portal struct (it's in TopPortalContext) */
pfree(portal);
+
+ /* Set is_plan_type_generic back to the original value. */
+ is_plan_type_generic = is_prev_plan_type_generic;
}
/*
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index ff09c63a02..1145d250cb 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -34,6 +34,10 @@ typedef enum
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN
} PlanCacheMode;
+/* Set whether the current and previous plan is generic or not */
+extern bool is_plan_type_generic;
+extern bool is_prev_plan_type_generic;
+
/* GUC parameter */
extern int plan_cache_mode;
@@ -158,6 +162,7 @@ typedef struct CachedPlan
int generation; /* parent's generation number for this plan */
int refcount; /* count of live references to this struct */
MemoryContext context; /* context containing this CachedPlan */
+ bool is_generic; /* is this plan generic or not? */
} CachedPlan;
/*
