>> 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
