Hi Fabien,
Thank you for your time. Apologies for not being more specific about my testing methodology.
> > PGSS not loaded: 0.18ms
>
> This means 0.0018 ms latency per transaction, which seems rather fast, on my laptop I have typically 0.0XX ms...
This actually means 0.18 milliseconds. I agree that this is a bit high, so I instead created an Ubuntu VM to get
resultsthat would align with yours.
> I could not reproduce these results on my ubuntu laptop. Could you be more precise about the test? Did you use
pgbench?Did it run in parallel? What options were used? What is the test script?
I did not use pgbench. It is important to call pg_stat_statements_reset before every query. This simulates a user that
isperforming distinct and non-repeated queries on their database. If you use prepared statements or the same set of
querieseach time, you would remove the contention on the pgss query text file.
I re-tested this on an Ubuntu machine with 4cores and 14GB ram. I did not run it in parallel. I used a python script
thatimplements the follow logic:
- select pg_stat_statements_reset() -- this is important because we are making pgss treat the 'select 1' like a new
querywhich it has not cached into pgss_hash.
- time 'select 1'
Repeat 100 times for each configuration.
Here are my Ubuntu results:
pgss unloaded
Mean: 0.076
Standard Deviation: 0.038
pgss.track=none
Mean: 0.099
Standard Deviation: 0.040
pgss.track=top
Mean: 0.098
Standard Deviation: 0.107
pgss.track=none + patch
Mean: 0.078
Standard Deviation: 0.042
The results are less noticeable, but I still see about a 20% performance improvement here.
> There I have an impact of 10% in these ideal testing conditions wrt latency where the DB does basically nothing, thus
whichwould not warrant to disable pg_stat_statements given the great service this extension brings to performance
analysis.
I agree that pg_stat_statements should not be disabled based on these performance results.
> Note that this does not mean that the patch should not be applied, it looks like an oversight, but really I do not
havethe performance degradation you are suggesting.
I appreciate your input and I want to come up with a canonical test that makes this contention more obvious.
Unfortunately, it is difficult because the criteria that causes this slow down (large query sizes and distinct
non-repeatedqueries) are difficult to reproduce with pgbench. I would be open to any suggestions here.
So even though the performance gains in this specific scenario are not as great, do you still think it would make sense
tosubmit a patch like this?
--
Raymond Martin
ramarti@microsoft.com
Azure Database for PostgreSQL