Glenn, On 12/5/06 9:12 AM, "Glenn Sullivan" <glenn.sullivan@xxxxxxxxxxxxx> wrote: > I am wanting some ideas about improving the performance of ORDER BY in > our use. I have a DB on the order of 500,000 rows and 50 columns. > The results are always sorted with ORDER BY. Sometimes, the users end up > with a search that matches most of the rows. In that case, I have a > LIMIT 5000 to keep the returned results under control. However, the > sorting seems to take 10-60 sec. If I do the same search without the > ORDER BY, it takes about a second. > > I am currently on version 8.0.1 on Windows XP using a Dell Optiplex 280 > with 1Gb of ram. I have set sort_mem=100000 set. > > Any ideas? Upgrade to 8.1 or 8.2, there were very large performance improvements to the sort code made for 8.1+. Also, when you've upgraded, you can experiment with increasing work_mem to get better performance. At some value of work_mem (probably > 32MB) you will reach a plateau of performance, probably 4-5 times faster than what you see with 8.0. - Luke
