On 2020-07-06 22:16, Fujii Masao wrote:
On 2020/06/11 14:59, torikoshia wrote:
On 2020-06-10 18:00, Kyotaro Horiguchi wrote:
+ TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_plan",
This could be a problem if we showed the last plan in this view. I
think "last_plan_type" would be better.
+ if (prep_stmt->plansource->last_plan_type ==
PLAN_CACHE_TYPE_CUSTOM)
+ values[7] = CStringGetTextDatum("custom");
+ else if (prep_stmt->plansource->last_plan_type ==
PLAN_CACHE_TYPE_GENERIC)
+ values[7] = CStringGetTextDatum("generic");
+ else
+ nulls[7] = true;
Using swith-case prevents future additional type (if any) from being
unhandled. I think we are recommending that as a convension.
Thanks for your reviewing!
I've attached a patch that reflects your comments.
Thanks for the patch! Here are the comments.
Thanks for your review!
+ Number of times generic plan was choosen
+ Number of times custom plan was choosen
Typo: "choosen" should be "chosen"?
Thanks, fixed them.
+ <entry role="catalog_table_entry"><para
role="column_definition">
+ <structfield>last_plan_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Tells the last plan type was generic or custom. If the
prepared
+ statement has not executed yet, this field is null
+ </para></entry>
Could you tell me how this information is expected to be used?
I think that generic_plans and custom_plans are useful when
investigating
the cause of performance drop by cached plan mode. But I failed to get
how much useful last_plan_type is.
This may be an exceptional case, but I once had a case needed
to ensure whether generic or custom plan was chosen for specific
queries in a development environment.
Of course, we can know it from adding EXPLAIN and ensuring whether $n
is contained in the plan, but I feel using the view is easier to use
and understand.
Regards,
--
Atsushi Torikoshi
NTT DATA CORPORATION
From f2155b1a9f3b500eca490827dd044f5c0f704dd3 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi<torikos...@oss.nttdata.com>
Date: Wed, 8 Jul 2020 09:17:22 +0900
Subject: [PATCH] We didn't have an easy way to find how many times generic or
custom plans of a prepared statement have been executed. This patch exposes
such numbers of both plans and the last plan type in pg_prepared_statements.
From 08183ee7ef827761f1ed38d2ab62b4f8f748baca Mon Sep 17 00:00:00 2001
---
doc/src/sgml/catalogs.sgml | 28 +++++++++++++++
src/backend/commands/prepare.c | 29 ++++++++++++---
src/backend/utils/cache/plancache.c | 23 ++++++++----
src/include/catalog/pg_proc.dat | 6 ++--
src/include/utils/plancache.h | 12 ++++++-
src/test/regress/expected/prepare.out | 51 +++++++++++++++++++++++++++
src/test/regress/expected/rules.out | 7 ++--
src/test/regress/sql/prepare.sql | 15 ++++++++
8 files changed, 155 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 003d278370..146dc2208b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -10829,6 +10829,34 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
frontend/backend protocol
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>generic_plans</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times generic plan was chosen
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>custom_plans</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times custom plan was chosen
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_plan_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Tells the last plan type was generic or custom. If the prepared
+ statement has not executed yet, this field is null
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 80d6df8ac1..b4b1865d48 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -694,7 +694,8 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
/*
* This set returning function reads all the prepared statements and
- * returns a set of (name, statement, prepare_time, param_types, from_sql).
+ * returns a set of (name, statement, prepare_time, param_types, from_sql,
+ * generic_plans, custom_plans, last_plan_type).
*/
Datum
pg_prepared_statement(PG_FUNCTION_ARGS)
@@ -723,7 +724,7 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
* build tupdesc for result tuples. This must match the definition of the
* pg_prepared_statements view in system_views.sql
*/
- tupdesc = CreateTemplateTupleDesc(5);
+ tupdesc = CreateTemplateTupleDesc(8);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
TEXTOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
@@ -734,6 +735,12 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
REGTYPEARRAYOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "from_sql",
BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "generic_plans",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 7, "custom_plans",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_plan_type",
+ TEXTOID, -1, 0);
/*
* We put all the tuples into a tuplestore in one scan of the hashtable.
@@ -755,8 +762,8 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
hash_seq_init(&hash_seq, prepared_queries);
while ((prep_stmt = hash_seq_search(&hash_seq)) != NULL)
{
- Datum values[5];
- bool nulls[5];
+ Datum values[8];
+ bool nulls[8];
MemSet(nulls, 0, sizeof(nulls));
@@ -766,6 +773,20 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
values[3] = build_regtype_array(prep_stmt->plansource->param_types,
prep_stmt->plansource->num_params);
values[4] = BoolGetDatum(prep_stmt->from_sql);
+ values[5] = Int64GetDatumFast(prep_stmt->plansource->num_generic_plans);
+ values[6] = Int64GetDatumFast(prep_stmt->plansource->num_custom_plans);
+
+ switch (prep_stmt->plansource->last_plan_type)
+ {
+ case PLAN_CACHE_TYPE_CUSTOM:
+ values[7] = CStringGetTextDatum("custom");
+ break;
+ case PLAN_CACHE_TYPE_GENERIC:
+ values[7] = CStringGetTextDatum("generic");
+ break;
+ default:
+ nulls[7] = true;
+ }
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 75b475c179..fcba6c1361 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -218,7 +218,9 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
plansource->generation = 0;
plansource->generic_cost = -1;
plansource->total_custom_cost = 0;
+ plansource->num_generic_plans = 0;
plansource->num_custom_plans = 0;
+ plansource->last_plan_type = PLAN_CACHE_TYPE_NONE;
MemoryContextSwitchTo(oldcxt);
@@ -285,7 +287,9 @@ CreateOneShotCachedPlan(RawStmt *raw_parse_tree,
plansource->generation = 0;
plansource->generic_cost = -1;
plansource->total_custom_cost = 0;
+ plansource->num_generic_plans = 0;
plansource->num_custom_plans = 0;
+ plansource->last_plan_type = PLAN_CACHE_TYPE_NONE;
return plansource;
}
@@ -1213,12 +1217,16 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
{
/* Build a custom plan */
plan = BuildCachedPlan(plansource, qlist, boundParams, queryEnv);
- /* Accumulate total costs of custom plans, but 'ware overflow */
- if (plansource->num_custom_plans < INT_MAX)
- {
- plansource->total_custom_cost += cached_plan_cost(plan, true);
- plansource->num_custom_plans++;
- }
+ /* Accumulate total costs of custom plans */
+ plansource->total_custom_cost += cached_plan_cost(plan, true);
+
+ plansource->num_custom_plans++;
+ plansource->last_plan_type = PLAN_CACHE_TYPE_CUSTOM;
+ }
+ else
+ {
+ plansource->num_generic_plans++;
+ plansource->last_plan_type = PLAN_CACHE_TYPE_GENERIC;
}
Assert(plan != NULL);
@@ -1574,8 +1582,11 @@ CopyCachedPlan(CachedPlanSource *plansource)
/* We may as well copy any acquired cost knowledge */
newsource->generic_cost = plansource->generic_cost;
newsource->total_custom_cost = plansource->total_custom_cost;
+ newsource->num_generic_plans = plansource->num_generic_plans;
newsource->num_custom_plans = plansource->num_custom_plans;
+ newsource->last_plan_type = PLAN_CACHE_TYPE_NONE;
+
MemoryContextSwitchTo(oldcxt);
return newsource;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 38295aca48..61ef8a1560 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7746,9 +7746,9 @@
{ oid => '2510', descr => 'get the prepared statements for this session',
proname => 'pg_prepared_statement', prorows => '1000', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'record',
- proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool}',
- proargmodes => '{o,o,o,o,o}',
- proargnames => '{name,statement,prepare_time,parameter_types,from_sql}',
+ proargtypes => '', proallargtypes => '{text,text,timestamptz,_regtype,bool,int8,int8,text}',
+ proargmodes => '{o,o,o,o,o,o,o,o}',
+ proargnames => '{name,statement,prepare_time,parameter_types,from_sql,generic_plans,custom_plans,last_plan_type}',
prosrc => 'pg_prepared_statement' },
{ oid => '2511', descr => 'get the open cursors for this session',
proname => 'pg_cursor', prorows => '1000', proretset => 't',
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 522020d763..8cbdd1685b 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -34,6 +34,14 @@ typedef enum
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN
} PlanCacheMode;
+/* possible values for a plan type */
+typedef enum
+{
+ PLAN_CACHE_TYPE_NONE,
+ PLAN_CACHE_TYPE_CUSTOM,
+ PLAN_CACHE_TYPE_GENERIC
+} PlanCacheType;
+
/* GUC parameter */
extern int plan_cache_mode;
@@ -130,7 +138,9 @@ typedef struct CachedPlanSource
/* State kept to help decide whether to use custom or generic plans: */
double generic_cost; /* cost of generic plan, or -1 if not known */
double total_custom_cost; /* total cost of custom plans so far */
- int num_custom_plans; /* number of plans included in total */
+ int64 num_custom_plans; /* # of custom plans included in total */
+ int64 num_generic_plans; /* # of generic plans */
+ PlanCacheType last_plan_type; /* type of the last plan */
} CachedPlanSource;
/*
diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 3306c696b1..00e5e71fc1 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -58,12 +58,63 @@ SELECT name, statement, parameter_types FROM pg_prepared_statements;
PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
FROM pg_database WHERE datname = $1;
+-- the view should return the inital state
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+ name | generic_plans | custom_plans | last_plan_type
+------+---------------+--------------+----------------
+ q2 | 0 | 0 |
+(1 row)
+
+EXECUTE q2('postgres');
+ datname | datistemplate | datallowconn
+----------+---------------+--------------
+ postgres | f | t
+(1 row)
+
+EXECUTE q2('postgres');
+ datname | datistemplate | datallowconn
+----------+---------------+--------------
+ postgres | f | t
+(1 row)
+
+EXECUTE q2('postgres');
+ datname | datistemplate | datallowconn
+----------+---------------+--------------
+ postgres | f | t
+(1 row)
+
+EXECUTE q2('postgres');
+ datname | datistemplate | datallowconn
+----------+---------------+--------------
+ postgres | f | t
+(1 row)
+
+EXECUTE q2('postgres');
+ datname | datistemplate | datallowconn
+----------+---------------+--------------
+ postgres | f | t
+(1 row)
+
+-- until here, all the plans are custom
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+ name | generic_plans | custom_plans | last_plan_type
+------+---------------+--------------+----------------
+ q2 | 0 | 5 | custom
+(1 row)
+
EXECUTE q2('postgres');
datname | datistemplate | datallowconn
----------+---------------+--------------
postgres | f | t
(1 row)
+-- latest plan shoud be generic
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+ name | generic_plans | custom_plans | last_plan_type
+------+---------------+--------------+----------------
+ q2 | 1 | 5 | generic
+(1 row)
+
PREPARE q3(text, int, float, boolean, smallint) AS
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
ten = $3::bigint OR true = $4 OR odd = $5::int)
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b813e32215..a180e9fd2c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1428,8 +1428,11 @@ pg_prepared_statements| SELECT p.name,
p.statement,
p.prepare_time,
p.parameter_types,
- p.from_sql
- FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
+ p.from_sql,
+ p.generic_plans,
+ p.custom_plans,
+ p.last_plan_type
+ FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans, custom_plans, last_plan_type);
pg_prepared_xacts| SELECT p.transaction,
p.gid,
p.prepared,
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index 985d0f05c9..8112f8f6cd 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -34,8 +34,23 @@ PREPARE q2(text) AS
SELECT datname, datistemplate, datallowconn
FROM pg_database WHERE datname = $1;
+-- the view should return the inital state
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+
+EXECUTE q2('postgres');
+EXECUTE q2('postgres');
+EXECUTE q2('postgres');
+EXECUTE q2('postgres');
EXECUTE q2('postgres');
+-- until here, all the plans are custom
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+
+EXECUTE q2('postgres');
+
+-- latest plan shoud be generic
+SELECT name, generic_plans, custom_plans, last_plan_type from pg_prepared_statements;
+
PREPARE q3(text, int, float, boolean, smallint) AS
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
ten = $3::bigint OR true = $4 OR odd = $5::int)
--
2.18.1
