User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Tue, 05 Dec 2006 09:36:44 -0800
Subject: Re: Performance of ORDER BY
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Glenn Sullivan" <glenn.sullivan@varianinc.com>,
	pgsql-performance@postgresql.org
Message-ID: <C19AEE2C.F553%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Performance of ORDER BY
Thread-Index: AccYk+5DLJOTRoSHEduIcAAWy4o9DA==
In-Reply-To: <4575A863.6040504@varianinc.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Glenn,

On 12/5/06 9:12 AM, "Glenn Sullivan" <glenn.sullivan@varianinc.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