Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore

On 3 September 2015 at 16:50, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> On Wed, Sep 2, 2015 at 9:43 PM, David Rowley
> <david(dot)rowley(at)2ndquadrant(dot)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;

Master Patched
Median 12,499.03 12,465.21 (100.27%)
Average 12,504.87 12,468.91 (100.29%)

Test 3
create index t1_md5_idx on t1(md5);
Master Patched
Median 12,981.47 12,888.11 (100.72%)
Average 13,416.23 13,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?

Regards

David Rowley

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