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
<torikos...@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....@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
