Re: "SELECT ... FROM DUAL" is not quite as silly as it appears

On Sat, 5 Jan 2019 at 08:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> v5 attached; this responds to your comments plus Alexander's earlier
> gripe about not getting a clean build with --disable-cassert.
> No really substantive changes though.

I ran a few benchmarks on an AWS m5d.large instance based on top of
c5c7fa261f5. The biggest regression I see is from a simple SELECT 1 at
around 5-6%. A repeat of your test of SELECT 2+2 showed about half
that regression so the simple addition function call is introducing
enough overhead to lower the slowdown percentage by a good amount.
Test 3 improved performance a bit.

SELECT 1; I believe is a common query for some connection poolers as a
sort of ping to the database. In light of that, the performance drop
of 2 microseconds per query is not going to amount to very much in
total for that use case. i.e you'll need to do half a million pings
before it'll cost you 1 second of additional CPU time.

Results and tests are:

Setup: create table t1 (id int primary key);

Test 1: explain select 1;

Unpatched:

$ pgbench -n -f bench1.sql -T 60 postgres
tps = 30899.599603 (excluding connections establishing)
tps = 30806.247429 (excluding connections establishing)
tps = 30330.971411 (excluding connections establishing)

Patched:

tps = 28971.551297 (excluding connections establishing)
tps = 28892.053072 (excluding connections establishing)
tps = 28881.105928 (excluding connections establishing)

(5.75% drop)

Test 2: explain select * from t1 inner join (select 1 as x) x on t1.id=x.x;

Unpatched:

$ pgbench -n -f bench2.sql -T 60 postgres
tps = 14340.027655 (excluding connections establishing)
tps = 14392.871399 (excluding connections establishing)
tps = 14335.615020 (excluding connections establishing)

Patched:
tps = 14269.714239 (excluding connections establishing)
tps = 14305.901601 (excluding connections establishing)
tps = 14261.319313 (excluding connections establishing)

(0.54% drop)

Test 3: explain select * from t1 left join (select 1 as x) x on t1.id=x.x;

Unpatched:

$ pgbench -n -f bench3.sql -T 60 postgres
tps = 11404.769545 (excluding connections establishing)
tps = 11477.229511 (excluding connections establishing)
tps = 11365.426342 (excluding connections establishing)

Patched:
tps = 11624.081759 (excluding connections establishing)
tps = 11649.150950 (excluding connections establishing)
tps = 11571.724571 (excluding connections establishing)

(1.74% gain)

Test 4: explain select * from t1 inner join (select * from t1) t2 on
t1.id=t2.id;

Unpatched:
$ pgbench -n -f bench4.sql -T 60 postgres
tps = 9966.796818 (excluding connections establishing)
tps = 9887.775388 (excluding connections establishing)
tps = 9906.681296 (excluding connections establishing)

Patched:
tps = 9845.451081 (excluding connections establishing)
tps = 9936.377521 (excluding connections establishing)
tps = 9915.724816 (excluding connections establishing)

(0.21% drop)

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services