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 2333432A2A8 for ; Sun, 3 Oct 2004 18:46:13 +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 18256-02 for ; Sun, 3 Oct 2004 17:46:01 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id CBCA5329EB2 for ; Sun, 3 Oct 2004 18:45:59 +0100 (BST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D15B3351E4; Sun, 3 Oct 2004 10:45:36 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id CFC24351A9; Sun, 3 Oct 2004 10:45:36 -0700 (PDT) Date: Sun, 3 Oct 2004 10:45:36 -0700 (PDT) From: Stephan Szabo To: Gaetano Mendola Cc: Scott Marlowe , pgsql-admin@postgresql.org Subject: Re: PLEASE GOD HELP US! In-Reply-To: <415FE6AD.80507@bigfoot.com> Message-ID: <20041003104231.M47800@megazone.bigpanda.com> References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> <1096691616.2611.22.camel@localhost.localdomain> <20041002080923.C99145@megazone.bigpanda.com> <1096751454.2611.33.camel@localhost.localdomain> <415FE6AD.80507@bigfoot.com> 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/71 X-Sequence-Number: 15064 On Sun, 3 Oct 2004, Gaetano Mendola wrote: > Scott Marlowe wrote: > > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote: > > > >>On Fri, 1 Oct 2004, Scott Marlowe wrote: > >> > >> > >>>On Fri, 2004-10-01 at 14:26, 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. > >>> > >>> > >>>Wouldn't this work just as well? > >>> > >>>SELECT * FROM thread_listing AS t ORDER BY t.status > >>>DESC,t.lastreply desc LIMIT 25 OFFSET 0 > >> > >>Probably not, because I don't think he wants the other statuses to have > >>special ranking over the others, so a status=4 and status=1 row should be > >>sorted by lastreply only effectively. This is the problem of combining > >>separate status flags into a single field if you want to be doing these > >>sorts of queries. > >> > > > > > > So would a union give good performance? Just union the first 25 or less > > with status=5 with the rest, using a 1 and 0 in each union to order by > > first? Hopefully the indexes would then be used. > > anyone seen that the OP is running the server with sequential scan disabled ? Yep, but he's doing some queries for which the indexes weren't useful unless he had some very specific ones, so it'd still be choosing sequential scans for those AFAICT (for example I believe the 9s get the first 25 threads query). I think someone'd already pointed out that running with sequential scan disabled is generally a bad idea, so I didn't think it was worth mentioning again.