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 B0643329FA1 for ; Sun, 3 Oct 2004 13:08:11 +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 42679-02 for ; Sun, 3 Oct 2004 12:08:03 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 6E2E5329F9F for ; Sun, 3 Oct 2004 13:08:04 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i93C81bx043257 for ; Sun, 3 Oct 2004 12:08:01 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i93BkqQq039646 for pgsql-admin@postgresql.org; Sun, 3 Oct 2004 11:46:52 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.admin Subject: Re: PLEASE GOD HELP US! Date: Sun, 03 Oct 2004 13:46:53 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 40 Message-ID: <415FE6AD.80507@bigfoot.com> References: <00ab01c4a7f4$f04fffd0$3402a8c0@shanepc> <1096691616.2611.22.camel@localhost.localdomain> <20041002080923.C99145@megazone.bigpanda.com> <1096751454.2611.33.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Scott Marlowe User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: <1096751454.2611.33.camel@localhost.localdomain> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-admin@postgresql.org 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/66 X-Sequence-Number: 15059 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 ? Reagards Gaetano Mendola