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

On 2021/02/08 14:02, torikoshia wrote:
> On 2021-02-04 11:19, Kyotaro Horiguchi wrote:
>> At Thu, 04 Feb 2021 10:16:47 +0900, torikoshia
>> <torikoshia@oss.nttdata.com> wrote in
>>> Chengxi Sun, Yamada-san, Horiguchi-san,
>>>
>>> Thanks for all your comments.
>>> Adding only the number of generic plan execution seems acceptable.
>>>
>>> On Mon, Jan 25, 2021 at 2:10 PM Kyotaro Horiguchi
>>> <horikyota.ntt@gmail.com> wrote:
>>> > Note that ActivePortal is the closest nested portal. So it gives the
>>> > wrong result for nested portals.
>>>
>>> I may be wrong, but I thought it was ok since the closest nested
>>> portal is the portal to be executed.
>>
>> After executing the inner-most portal, is_plan_type_generic has a
>> value for the inner-most portal and it won't be changed ever after. At
>> the ExecutorEnd of all the upper-portals see the value for the
>> inner-most portal left behind is_plan_type_generic nevertheless the
>> portals at every nest level are independent.
>>
>>> ActivePortal is used in ExecutorStart hook in the patch.
>>> And as far as I read PortalStart(), ActivePortal is changed to the
>>> portal to be executed before ExecutorStart().
>>>
>>> If possible, could you tell me the specific case which causes wrong
>>> results?
>>
>> Running a plpgsql function that does PREPRE in a query that does
>> PREPARE?
> 
> Thanks for your explanation!
> 
> I confirmed that it in fact happened.
> 
> To avoid it, attached patch preserves the is_plan_type_generic before changing it and sets it back at the end of pgss_ExecutorEnd().
> 
> Any thoughts?

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.


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()?

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,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION