Date: Mon, 1 Apr 2019 13:35:19 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1554150919882-0.post@n3.nabble.com>
In-Reply-To: 
 <CAOBaU_abWF8Q-qKr3f6Q1M3MN7nVrfvwF6P105C-kCds85Lzww@mail.gmail.com>
References: 
 <DB6PR0301MB21351B7CDA68EFE4838562EC905C0@DB6PR0301MB2135.eurprd03.prod.outlook.com>
 <CAOBaU_bU1m3_XF5qKYtSj1ua4dxd=FWDyh2SH4rSJAUUfsGmAQ@mail.gmail.com>
 <DB6PR0301MB2135B8CE071A02D2D663CFA1905F0@DB6PR0301MB2135.eurprd03.prod.outlook.com>
 <CAOBaU_aX2Ad_pcSWGUQ1YFk8gotwoMYJDg5Wmosh7jj+28AQ4w@mail.gmail.com>
 <1553718968249-0.post@n3.nabble.com>
 <CAOBaU_YY1paFNxPoeP7=WUZh5g92rSjMYLdnNqpNzyEiEAY9nw@mail.gmail.com>
 <1553726396218-0.post@n3.nabble.com>
 <7091891553759108@iva3-2961a207771d.qloud-c.yandex.net>
 <CAOBaU_YsFEKmU31JVjnuxkTgAPYaSKrKmCUh6Ea1EM_10=n3CQ@mail.gmail.com>
 <CAOBaU_abWF8Q-qKr3f6Q1M3MN7nVrfvwF6P105C-kCds85Lzww@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

Hi,

I have played with this patch, it works fine.

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

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)

notes:
- PostgreSQL 12devel on x86_64-w64-mingw32, compiled by gcc.exe 
(x86_64-win32-sehrev1, Built by MinGW-W64 project) 7.2.0, 64-bit,
- cpu usage was less that 95%,
- avg_tps is based on 3 runs,
- there was a wait of arround 1 minute between each run to keep 
  computer temperature and fan usage low.

Regards
PAscal


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html