On 3 September 2015 at 16:50, Peter Geoghegan <pg@heroku.com> wrote:
On Wed, Sep 2, 2015 at 9:43 PM, David Rowley <david.rowley@2ndquadrant.com> wrote: > Peter, would you be able to share the test case which you saw the speedup > on. So far I've been unable to see much of an improvement.
The case I tested was an internal sort CREATE INDEX. I don't recall the exact details, but testing showed it to be a fairly robust speedup. It was not a very brief CREATE INDEX operation, or a very lengthily one. trace_sort output made it quite visible that there was a significant saving after the sort is "performed", but before it is "done". It wasn't hard to see an improvement on a variety of other cases, although the Intel vTune tool made the difference particularly obvious.
The only thing that definitely won't be helped is pass-by-value datum sort cases. In case it matters, I used GCC 4.8.
My test cases are:
set work_mem ='1GB';
create table t1 as select md5(random()::text) from generate_series(1,10000000);
Times are in milliseconds. Median and average over 10 runs.
Test 1
select count(distinct md5) from t1;
Master Patched Median 10,965.77 10,986.30 (99.81%) Average 10,983.63 11,013.55 (99.73%)
Test 2
select sum(rn) from (select row_number() over (order by md5) rn from t1) a;
MasterPatched
Median12,499.0312,465.21 (100.27%)
Average12,504.8712,468.91 (100.29%)
Test 3
create index t1_md5_idx on t1(md5);
MasterPatched
Median12,981.4712,888.11 (100.72%)
Average13,416.2313,249.32 (101.26%)
gcc version 4.8.3
Intel(R) Xeon(R) CPU E5-2630 v3
Are you seeing any speedup from any of these on your hardware?
