Sort performance

> I'm not sure if this is good news or bad news. Either some kudos are due to
> the gang that worked on the external sort performance or something's very
> wrong with the qsort implementation in glibc because I'm seeing Postgres's
> external sort perform better than qsort.

And here's a really perverse case. The external sort runs in 740 milliseconds
but qsort takes over 2 seconds:

postgres=# select count(*) from (select * from (select * from w5 limit 200000) as x order by w ) as x;
count
--------
200000
(1 row)

Time: 740.324 ms
postgres=# set work_mem = '12MB';
SET
Time: 0.145 ms
postgres=# select count(*) from (select * from (select * from w5 limit 200000) as x order by w ) as x;
count
--------
200000
(1 row)

Time: 2051.317 ms

LOG: statement: set work_mem = '11MB';

LOG: statement: select count(*) from (select * from (select * from w5 limit 200000) as x order by w ) as x;
LOG: begin tuple sort: nkeys = 1, workMem = 11264, randomAccess = f
LOG: switching to external sort with 41 tapes: CPU 0.01s/0.04u sec elapsed 0.05 sec
LOG: performsort starting: CPU 0.01s/0.34u sec elapsed 0.35 sec
LOG: finished writing run 1 to tape 0: CPU 0.01s/0.52u sec elapsed 0.54 sec
LOG: finished writing final run 2 to tape 1: CPU 0.01s/0.60u sec elapsed 0.62 sec
LOG: performsort done (except 2-way final merge): CPU 0.01s/0.63u sec elapsed 0.65 sec
LOG: external sort ended, 593 disk blocks used: CPU 0.02s/0.71u sec elapsed 0.73 sec

LOG: statement: set work_mem = '12MB';

LOG: statement: select count(*) from (select * from (select * from w5 limit 200000) as x order by w ) as x;
LOG: begin tuple sort: nkeys = 1, workMem = 12288, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.06u sec elapsed 0.06 sec
LOG: doing qsort of 200000 tuples
LOG: performsort done: CPU 0.00s/1.99u sec elapsed 2.00 sec
LOG: internal sort ended, 11919 KB used: CPU 0.00s/2.03u sec elapsed 2.04 sec

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com