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 5660932A1F5 for ; Fri, 1 Oct 2004 22:43:03 +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 66265-09 for ; Fri, 1 Oct 2004 21:42:57 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 856A132A1B5 for ; Fri, 1 Oct 2004 22:42:58 +0100 (BST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 84DF8357DB; Fri, 1 Oct 2004 14:42:58 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 8351C357DA; Fri, 1 Oct 2004 14:42:58 -0700 (PDT) Date: Fri, 1 Oct 2004 14:42:58 -0700 (PDT) From: Stephan Szabo To: Michael Paesold Cc: Shane|SkinnyCorp , PgSQL ADMIN Subject: Re: PLEASE GOD HELP US! In-Reply-To: <045401c4a7fc$c9433a80$ad01a8c0@zaphod> Message-ID: <20041001144100.E67126@megazone.bigpanda.com> References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> <045401c4a7fc$c9433a80$ad01a8c0@zaphod> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/36 X-Sequence-Number: 15030 On Fri, 1 Oct 2004, Michael Paesold wrote: > 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 Actually, I think he'd want an expression index on ((status=5),lastreply). In simple tests (admittedly on 8.0b3) it looks like such an index can be used rather than a separate sort step.