Re: WIP: Upper planner pathification

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