Date: Fri, 22 May 2020 12:27:31 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1590175651060-0.post@n3.nabble.com>
In-Reply-To: 
 <CAOBaU_ZTQTxB56zz+UOd1xikNJjopOAfZ6PMY8dxkcEZC3fGrA@mail.gmail.com>
References: <5d54ce90-6807-83f9-aec2-4100152acab0@oss.nttdata.com>
 <CAOBaU_Zhe4qmfhGR42LVsRHXWstbnsXzmVVEwxnnDUEp1wxfTg@mail.gmail.com>
 <d2b76c80-11a7-f8d4-1d39-8c2ee9da5a94@oss.nttdata.com>
 <CAKU4AWq=S+4F=Q=O8-FHXEURJEmDQ8-1tX8d1sfghn7OFHhMbg@mail.gmail.com>
 <CAOBaU_YDvPg=9Yqf_N-Ds=idt4QfM9=DuUxv=QOwBSTSDaHMrQ@mail.gmail.com>
 <20200521071716.GM2355@paquier.xyz>
 <CAOBaU_a9yz8Mys+SgM_ww+_LTcFrZ8J3sCs+nBzq5bTwF8BUZw@mail.gmail.com>
 <CAKU4AWr++rKSJRXKxi7K=pyMyx5VWo+VcM4_fHEMy2j2=_sKBQ@mail.gmail.com>
 <fd347b63-45d8-7f76-6403-f709aa7b5f13@oss.nttdata.com>
 <CAOBaU_ZTQTxB56zz+UOd1xikNJjopOAfZ6PMY8dxkcEZC3fGrA@mail.gmail.com>
Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

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