From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Sort performance |
Date: | 2006-09-01 15:02:23 |
Message-ID: | 87lkp3iq9s.fsf@stark.enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul B. Anderson | 2006-09-01 15:04:36 | Re: Vacuum error on database postgres |
Previous Message | Martijn van Oosterhout | 2006-09-01 14:55:14 | Re: [pgsql-advocacy] Thought provoking piece on |