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

On 11 October 2015 at 13:20, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> It's worth considering that for some (possibly legitimate) reason, the
> built-in function call is ignored by your compiler, since GCC has
> license to do that. You might try this on both master and patched
> builds:
>
>
You're right, gcc did not include the prefetch instructions.
I've tested again on the same machine but with clang 3.7 instead of gcc
4.8.3

I've conducted the same tests again. All times are in milliseconds. Results
are the average and median over 10 runs.

set work_mem ='1GB';
create table t1 as select md5(random()::text) from
generate_series(1,10000000);
vacuum freeze t1;

Running 1 query at a time the results are as follows:

Test1: select count(distinct md5) from t1;

Master Patched Gain
Average 10853.679 10132.544 107.12%
Median 10754.193 10005.001 107.49%

Test2: select sum(rn) from (select row_number() over (order by md5) rn from
t1) a;
Master Patched Gain
Average 11495.8703 11475.0081 100.18%
Median 11495.6015 11455.944 100.35%

Test3: create index t1_md5_idx on t1(md5);
Master Patched Gain
Average 36464.4632 37830.3879 96.39%
Median 35946.608 36765.0055 97.77%

I also decided to run multiple queries at once, to see if there was any
cache pollution problems with the prefetching.

Test 1 pgbench -T 600 -c 16 -j 16 -f test1.sql -n
Test 2 pgbench -T 600 -c 16 -j 16 -f test2.sql -n

(tps output from pgbench was converted to milliseconds with 1/TPS*1000)
Master Patched Gain
Test 1 1375.413 1358.494 101.25%
Test 2 1594.753 1588.340 100.40%

CPU: 1 x Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz

I've attached a spreadsheet with all of the results.

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