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,