Re: Planning counters in pg_stat_statements (using pgss_store) - Mailing list pgsql-hackers

From Julien Rouhaud
Subject Re: Planning counters in pg_stat_statements (using pgss_store)
Date
Msg-id CAOBaU_aYyZ2GEyb4QTmrE9O5k0C1n0aDLbRCaH771sSn0eq7VA@mail.gmail.com
Whole thread Raw
In response to Re: Planning counters in pg_stat_statements (using pgss_store)  (legrand legrand <legrand_legrand@hotmail.com>)
Responses RE: Planning counters in pg_stat_statements (using pgss_store)
List pgsql-hackers
On Mon, Apr 1, 2019 at 10:35 PM legrand legrand
<legrand_legrand@hotmail.com> wrote:
>
> I have played with this patch, it works fine.

Thanks for testing!

> rem the last position of the "new" total_time column is confusing
> +CREATE VIEW pg_stat_statements AS
> +  SELECT *, total_plan_time + total_exec_time AS total_time
> +    FROM pg_stat_statements(true);

Yes, there are quite a lot of fields in pg_stat_statements(), so I
added the total_time as the last field to avoid enumerating all of
them.  I can change that if needed.


> I wanted to perform some benchmark between those 4 cases:
> 0 - no pgss,
> 1 - original pgss (no planning counter and 1 access to pgss hash),
> 2 - pggs reading querytext in planner hook (* 2 accesses to pgss hash),
> 3 - pggs reading querytext in parse hook (* 3 accesses to pgss hash)
>
> It seems that the difference is so tiny, that there was no other way than
> running
> minimal "Select 1;" statement ...
>
> ./pgbench -c 10 -j 5 -t 500000 -f select1stmt.sql postgres
>
> case  avg_tps   pct_diff
> 0        89 278   --
> 1        88 745   0,6%
> 2        88 282   1,1%
> 3        86 660   2,9%
>
> This means that even in this extrem test case, the worst degradation is less
> than 3%
> (this overhead can be removed using pg_stat_statements.track_planning guc)

Is the difference between 2 and 3 the extraneous pgss_store call to
always store the query text if planner hook doesn't have access to the
query text?



pgsql-hackers by date:

Previous
From: "Yuzuko Hosoya"
Date:
Subject: RE: Problem with default partition pruning
Next
From: Michael Paquier
Date:
Subject: Re: Re: A separate table level option to control compression