X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8A72E32A156 for ; Fri, 1 Oct 2004 22:22:46 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64723-01 for ; Fri, 1 Oct 2004 21:22:40 +0000 (GMT) Received: from mail.gmx.net (imap.gmx.net [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id 244D132A1A2 for ; Fri, 1 Oct 2004 22:22:39 +0100 (BST) Received: (qmail 25414 invoked by uid 65534); 1 Oct 2004 21:22:39 -0000 Received: from 83-64-198-240.dynamic.xdsl-line.inode.at (EHLO zaphod) (83.64.198.240) by mail.gmx.net (mp023) with SMTP; 01 Oct 2004 23:22:39 +0200 X-Authenticated: #1946847 Message-ID: <045401c4a7fc$c9433a80$ad01a8c0@zaphod> From: "Michael Paesold" To: "Shane | SkinnyCorp" , "PgSQL ADMIN" References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> Subject: Re: PLEASE GOD HELP US! Date: Fri, 1 Oct 2004 23:22:39 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/33 X-Sequence-Number: 15027 Shane | SkinnyCorp wrote: > Okay, just so no one posts about this again... > > the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads > with a status of '5' to the top of the list... it is NOT meant to only > grab > threads where the status = 5. Oh and believe me, when I take this out of > the query, it CERTAINLY doesn't add any more than possible 1/4 of a > millesecond to the speed of the SELECT statement. > > :/ Perhaps I missed it, but you did not yet send the output of the explain of this query, did you? Could you please do: EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC, t.lastreply DESC LIMIT 25 OFFSET 0; EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0; EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25 OFFSET 0; (and post the results here) The first one will certainly do a sequential scan, the last one will use an index if available. For the second you will need a partial index on lastreply with a where clause WHERE status=5, I believe. So a solution would perhaps be to use two queries, one with WHERE t.status=5, another without. Make both use an index. Then combine them in your application. Of course this can only work if you do not need an offset other then 0. Also, did you check that your system does not swap (using vmstat)? Best Regards, Michael Paesold