From: | "Luke Lonergan" <llonergan(at)greenplum(dot)com> |
---|---|
To: | "Glenn Sullivan" <glenn(dot)sullivan(at)varianinc(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Performance of ORDER BY |
Date: | 2006-12-05 17:36:44 |
Message-ID: | C19AEE2C.F553%llonergan@greenplum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Glenn,
On 12/5/06 9:12 AM, "Glenn Sullivan" <glenn(dot)sullivan(at)varianinc(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-12-05 18:02:06 | Re: Performance of ORDER BY |
Previous Message | Glenn Sullivan | 2006-12-05 17:12:03 | Performance of ORDER BY |