Re: WIP: Upper planner pathification - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: WIP: Upper planner pathification
Date
Msg-id 56D96781.1090305@2ndquadrant.com
Whole thread Raw
In response to Re: WIP: Upper planner pathification  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Hi,

On 03/03/2016 01:10 AM, Tom Lane wrote:
> Hmmm ... I'm now wondering about the "measurement error" theory.
> I tried to repeat this measurement locally, focusing on the select-only
> number since that should have a higher ratio of planning time to
> execution.
>
> Test setup:
> cassert-off build
> pgbench -i -s 100
> sudo cpupower frequency-set --governor performance
>
> repeat 3 times: pgbench -c 4 -j 4 -P 5 -T 60 -S
>
> HEAD:
> tps = 32508.217002 (excluding connections establishing)
> tps = 33081.402766
> tps = 32520.859913
> average of 3: 32703 tps
>
> WITH PATCH:
> tps = 32815.922160 (excluding connections establishing)
> tps = 33312.149718
> tps = 32784.527489
> average of 3: 32970 tps
>
> (Hardware: dual quad-core Xeon E5-2609, running current RHEL6)
>
> So I see no evidence for a slowdown on pgbench's SELECT queries.
> Anybody else want to check performance on simple scan/join queries?

I did a small test today, mostly out of curiosity. And I think that
while the results are a bit noisy, there's a clear slowdown. But it's
extremely small, like ~0.5% for median/average, so I'd say it's negligible.

I used the i5-2500k machine I use for this kind of tests, and I did 30
runs of

    pgbench -S -T 60 pgbench

on scale 10 database (analyzed and frozen before), both with and without
the patch applied. FWIW the machine is one of the least noisy ones when
it comes to such benchmarks.

The results look like this:

               master    patched    diff
     ---------------------------------------
     median    16531    16459     -0.4%
     average   16526    16473     -0.3%

It's a bit more obvious when doing a scatter plot of the results (after
sorting each time series) with master on x-axis and patched on y-axis.
Ideally, we'd get the data points on a diagonal (no change) or above it
(speedup), but the points are actually below. See the chart attached.

But I do agree this is mostly at the noise level, pretty good for a
first cut that intentionally does not include any hacks. It's definitely
way below the benefits of this patch, +1 to applying this sooner than later.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment

pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: RFC: replace pg_stat_activity.waiting with something more descriptive
Next
From: "MauMau"
Date:
Subject: Greeting for coming back, and where is PostgreSQL going