> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
