Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8FE7A9F9FDA for ; Tue, 5 Dec 2006 13:18:22 -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 02171-09 for ; Tue, 5 Dec 2006 13:18:11 -0400 (AST) X-Greylist: delayed 00:06:02.990527 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 D39889F938C for ; Tue, 5 Dec 2006 13:18:10 -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 KAA09329 for ; Tue, 5 Dec 2006 10:12:03 -0700 (MST) Message-ID: <4575A863.6040504@varianinc.com> Date: Tue, 05 Dec 2006 10:12:03 -0700 From: Glenn Sullivan 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: pgsql-performance@postgresql.org Subject: Performance of ORDER BY Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200612/83 X-Sequence-Number: 22139 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? Thanks, Glenn