Hi Torikoshi-san!
On 2020/05/21 17:10, Kyotaro Horiguchi wrote:
At Thu, 21 May 2020 12:18:16 +0900, Fujii Masao <masao.fu...@oss.nttdata.com>
wrote in
On 2020/05/20 21:56, Atsushi Torikoshi wrote:
On Wed, May 20, 2020 at 1:32 PM Kyotaro Horiguchi
<horikyota....@gmail.com <mailto:horikyota....@gmail.com>> wrote:
At Tue, 19 May 2020 22:56:17 +0900, Atsushi Torikoshi
<ato...@gmail.com <mailto:ato...@gmail.com>> wrote in
> On Sat, May 16, 2020 at 6:01 PM legrand legrand
> <legrand_legr...@hotmail.com <mailto:legrand_legr...@hotmail.com>>
> wrote:
>
> BTW, I'd also appreciate other opinions about recording the number
> of generic and custom plans on pg_stat_statemtents.
If you/we just want to know how a prepared statement is executed,
couldn't we show that information in pg_prepared_statements view?
=# select * from pg_prepared_statements;
-[ RECORD 1 ]---+----------------------------------------------------
name | stmt1
statement | prepare stmt1 as select * from t where b = $1;
prepare_time | 2020-05-20 12:01:55.733469+09
parameter_types | {text}
from_sql | t
exec_custom | 5 <- existing num_custom_plans
exec_total | 40 <- new member of CachedPlanSource
Thanks, Horiguchi-san!
Adding counters to pg_prepared_statements seems useful when we want
to know the way prepared statements executed in the current session.
I like the idea exposing more CachedPlanSource fields in
pg_prepared_statements. I agree it's useful, e.g., for the debug
purpose.
This is why I implemented the similar feature in my extension.
Please see [1] for details.
Thanks. I'm not sure plan_cache_mode should be a part of the view.
Cost numbers would look better if it is cooked a bit. Is it worth
being in core?
=# select * from pg_prepared_statements;
-[ RECORD 1 ]---+--------------------------------------------
name | p1
statement | prepare p1 as select a from t where a = $1;
prepare_time | 2020-05-21 15:41:50.419578+09
parameter_types | {integer}
from_sql | t
calls | 7
custom_calls | 5
plan_generation | 6
generic_cost | 4.3100000000000005
custom_cost | 9.31
Perhaps plan_generation is not needed there.
I tried to creating PoC patch too, so I share it.
Please find attached file.
# Test case
prepare count as select count(*) from pg_class where oid >$1;
execute count(1); select * from pg_prepared_statements;
-[ RECORD 1 ]---+--------------------------------------------------------------
name | count
statement | prepare count as select count(*) from pg_class where oid >$1;
prepare_time | 2020-05-21 17:41:16.134362+09
parameter_types | {oid}
from_sql | t
is_generic_plan | f <= False
You can see the following result, when you execute it 6 times.
-[ RECORD 1 ]---+--------------------------------------------------------------
name | count
statement | prepare count as select count(*) from pg_class where oid >$1;
prepare_time | 2020-05-21 17:41:16.134362+09
parameter_types | {oid}
from_sql | t
is_generic_plan | t <= True
Thanks,
Tatsuro Yamada
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 80d6df8ac1..63de4fdb78 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -723,7 +723,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(6);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
TEXTOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
@@ -734,6 +734,8 @@ pg_prepared_statement(PG_FUNCTION_ARGS)
REGTYPEARRAYOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "from_sql",
BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "is_generic_plan",
+ BOOLOID, -1, 0);
/*
* We put all the tuples into a tuplestore in one scan of the hashtable.
@@ -755,8 +757,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[6];
+ bool nulls[6];
MemSet(nulls, 0, sizeof(nulls));
@@ -766,6 +768,7 @@ 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] = BoolGetDatum(prep_stmt->plansource->gplan);
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..b1d2d4cd37 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,bool}',
+ proargmodes => '{o,o,o,o,o,o}',
+ proargnames =>
'{name,statement,prepare_time,parameter_types,from_sql,is_generic_plan}',
prosrc => 'pg_prepared_statement' },
{ oid => '2511', descr => 'get the open cursors for this session',
proname => 'pg_cursor', prorows => '1000', proretset => 't',
