pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-hackers@postgresql.org
Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
Date: Fri, 22 May 2020 12:27:31 -0700 (MST)
Message-ID: <1590175651060-0.post@n3.nabble.com> (raw)
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>

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





view thread (127+ messages)  latest in thread

Message-ID: <1590175651060-0.post@n3.nabble.com>
Permalink:  ../1590175651060-0.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1590175651060-0.post@n3.nabble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: legrand_legrand@hotmail.com
  Subject: Re: Planning counters in pg_stat_statements (using pgss_store)
  In-Reply-To: <1590175651060-0.post@n3.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox