| From: | Glenn Sullivan <glenn(dot)sullivan(at)varianinc(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Performance of ORDER BY |
| Date: | 2006-12-05 20:42:56 |
| Message-ID: | 4575D9D0.5010206@varianinc.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
<!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. I guess this was good timing
given that it looks like<br>
8.2 was just released today. I will upgade to that before doing
anything else.<br>
<br>
Glenn<br>
<br>
Tom Lane wrote:
<blockquote cite="mid7090(dot)1165341726(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Glenn Sullivan <a class="moz-txt-link-rfc2396E" href="mailto:glenn(dot)sullivan(at)varianinc(dot)com"><glenn(dot)sullivan(at)varianinc(dot)com></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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 1.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alexandru Coseru | 2006-12-05 21:44:33 | Re: Hardware advice |
| Previous Message | Stefan Kaltenbrunner | 2006-12-05 19:55:40 | Re: Performance of ORDER BY |