Re: Is it useful to record whether plans are generic or custom?

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,