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: Mon, 1 Apr 2019 13:35:19 -0700 (MST)
Message-ID: <1554150919882-0.post@n3.nabble.com> (raw )
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 >
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
view thread (127+ messages) latest in thread
Message-ID: <1554150919882-0.post@n3.nabble.com>
Permalink: ../1554150919882-0.post@n3.nabble.com/
Also on: postgresql.org/message-id/1554150919882-0.post@n3.nabble.com
copy link · copy postgr.es
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: <1554150919882-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