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. Regards, -- Atsushi Torikoshi
From f2155b1a9f3b500eca490827dd044f5c0f704dd3 Mon Sep 17 00:00:00 2001 From: Atsushi Torikoshi<[email protected]> Date: Thu, 11 Jun 2020 11:35:25 +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. fixed comments --- 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 700271fd40..ac9e56d5b3 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 choosen + </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 choosen + </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 61f2c2f5b4..31a568d0cf 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -7743,9 +7743,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
