Re: Planning counters in pg_stat_statements (using pgss_store)

Hello

I think the most important question for this topic is performance penalty.
It was a long story, first test on my desktop was too volatile. I setup separate PC with DB only and test few cases.

PC spec: 2-core Intel Core 2 Duo E6550, 4GB ram, mechanical HDD
All tests on top 7dedfd22b79822b7f4210e6255b672ea82db6678 commit, build via ./configure  --prefix=/home/melkij/tmp/
--enable-tap-tests
DB settings:
  listen_addresses = '*'
  log_line_prefix = '%m %p %u@%d from %h [vxid:%v txid:%x] [%i] '
  lc_messages = 'C'
  shared_buffers = 512MB

pgbench runned from different host, in same L2 network.
Database was generated by: pgbench -s 10 -i -h hostname postgres
After database start I run:
  create extension if not exists pg_prewarm;
  select count(*), sum(pg_prewarm) from pg_tables join pg_prewarm(tablename::regclass) on true where schemaname=
'public';
  select count(*), sum(pg_prewarm) from pg_indexes join pg_prewarm(indexname::regclass) on true where schemaname=
'public';
So all data was in buffers.

Load generated by command: pgbench --builtin=select-only --time=300 -n -c 10 -h hostname postgres -M (vary)

Tests are:
head_no_pgss - unpatched version, empty shared_preload_libraries
head_track_none - unpatched version with:
  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.max = 5000
  pg_stat_statements.track = none
  pg_stat_statements.save = off
  pg_stat_statements.track_utility = off
head_track_top - the same but with pg_stat_statements.track=top
5-times runned in every mode -M: simple, extended, prepared

patch_not_loaded - build with latest published patches, empty shared_preload_libraries
patch_track_none - patched build with
  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.max = 5000
  pg_stat_statements.track = none
  pg_stat_statements.save = off
  pg_stat_statements.track_utility = off
  pg_stat_statements.track_planning = off
patch_track_top - the same but with pg_stat_statements.track=top
patch_track_planning - with:
  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.max = 5000
  pg_stat_statements.track = top
  pg_stat_statements.save = off
  pg_stat_statements.track_utility = off
  pg_stat_statements.track_planning = on

10-times runned in every mode -M: simple, extended, prepared

Results:

         test         |   mode   | average_tps | degradation_perc 
----------------------+----------+-------------+------------------
 head_no_pgss         | extended |       13816 |            1.000
 patch_not_loaded     | extended |       13755 |            0.996
 head_track_none      | extended |       13607 |            0.985
 patch_track_none     | extended |       13560 |            0.981
 head_track_top       | extended |       13277 |            0.961
 patch_track_top      | extended |       13189 |            0.955
 patch_track_planning | extended |       12983 |            0.940
 head_no_pgss         | prepared |       29101 |            1.000
 head_track_none      | prepared |       28510 |            0.980
 patch_track_none     | prepared |       28481 |            0.979
 patch_not_loaded     | prepared |       28382 |            0.975
 patch_track_planning | prepared |       28046 |            0.964
 head_track_top       | prepared |       28035 |            0.963
 patch_track_top      | prepared |       27973 |            0.961
 head_no_pgss         | simple   |       16733 |            1.000
 patch_not_loaded     | simple   |       16552 |            0.989
 head_track_none      | simple   |       16452 |            0.983
 patch_track_none     | simple   |       16365 |            0.978
 head_track_top       | simple   |       15867 |            0.948
 patch_track_top      | simple   |       15820 |            0.945
 patch_track_planning | simple   |       15739 |            0.941

So I found slight slowdown with track_planning = off compared to HEAD. Possibly just at the level of measurement error.
Ithink this is ok.
 
track_planning = on also has no dramatic impact. In my opinion proposed design with pgss_store call is acceptable.

regards, Sergei