Re: Planning counters in pg_stat_statements (using pgss_store)

>> If we can store the plan for each statement, e.g., like pg_store_plans
>> extension [1] does, rather than such partial information, which would
>> be enough for your cases?

> That'd definitely address way more use cases. Do you know if some
> benchmark were done to see how much overhead such an extension adds?

Hi Julien,
Did you asked about how overhead Auto Explain adds ?

The only extension that was proposing to store plans with a decent planid
calculation was pg_stat_plans that is not compatible any more with recent
pg versions for years.

We all know here that pg_store_plans, pg_show_plans, (my) pg_stat_sql_plans
use ExplainPrintPlan through Executor Hook, and that Explain is slow ...

Explain is slow because it was not designed for performances:
1/ colname_is_unique
see
https://www.postgresql-archive.org/Re-Explain-is-slow-with-tables-having-many-columns-td6047284.html

2/ hash_create from set_rtable_names
Look with perf top about
do $$ declare i int; begin for i in 1..1000000 loop execute 'explain
select 1'; end loop end; $$;

I may propose a "minimal" explain that only display explain's backbone and
is much faster
see
https://github.com/legrandlegrand/pg_stat_sql_plans/blob/perf-explain/pgssp_explain.c

3/ All those extensions rebuild the explain output even with cached plan
queries ...
a way to optimize this would be to build a planid during planning (using
associated hook)

4/ All thoses extensions try to rebuild the explain plan even for trivial
queries/plans
like "select 1" or " insert into t values (,,,)" and that's not great for
high transactional
applications ...

So yes, pg_store_plans is one of the short term answers to Andy Fan needs,
the answer for the long term would be to help extensions to build planid and
store plans,
by **adding a planid field in plannedstmt memory structure ** and/or
optimizing explain command;o)

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html