Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DEF9D9F9C9E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  5 Dec 2006 16:43:12 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 34319-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  5 Dec 2006 16:43:05 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from k2.nmr.varianinc.com (host-70-69.varianinc.com [4.22.70.69])
	by postgresql.org (Postfix) with ESMTP id CB0739F938C
	for <pgsql-performance@postgresql.org>;
	Tue,  5 Dec 2006 16:43:04 -0400 (AST)
Received: from [10.190.213.112] (khaos [10.190.213.112])
	by k2.nmr.varianinc.com (8.9.3+Sun/8.9.1) with ESMTP id NAA20321;
	Tue, 5 Dec 2006 13:42:59 -0700 (MST)
Message-ID: <4575D9D0.5010206@varianinc.com>
Date: Tue, 05 Dec 2006 13:42:56 -0700
From: Glenn Sullivan <glenn.sullivan@varianinc.com>
Organization: Chemagnetics/Varian NMR
User-Agent: Mozilla Thunderbird 1.0.7-1.4.1 (X11/20050929)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance of ORDER BY
References: <4575A863.6040504@varianinc.com> <7090.1165341726@sss.pgh.pa.us>
In-Reply-To: <7090.1165341726@sss.pgh.pa.us>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.161 tagged_above=0 required=5 tests=AWL, BAYES_50,
	HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY, MIME_HTML_ONLY
X-Spam-Level: 
X-Archive-Number: 200612/95
X-Sequence-Number: 22151

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks to Luke and Tom for the input.&nbsp; I guess this was good timing
given that it looks like<br>
8.2 was just released today.&nbsp;&nbsp; I will upgade to that before doing
anything else.<br>
<br>
Glenn<br>
<br>
Tom Lane wrote:
<blockquote cite="mid7090.1165341726@sss.pgh.pa.us" type="cite">
  <pre wrap="">Glenn Sullivan <a class="moz-txt-link-rfc2396E" href="mailto:glenn.sullivan@varianinc.com">&lt;glenn.sullivan@varianinc.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.  
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Does the ORDER BY match an index?  If so, is it using the index?
(See EXPLAIN.)

  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
In 8.0 that might be counterproductively high --- we have seen cases
where more sort_mem = slower with the older sorting code.  I concur
with Luke's advice that you should update to 8.2 (not 8.1) to get the
improved sorting code.

			regards, tom lane

  </pre>
</blockquote>
</body>
</html>