X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C6B239DC896 for ; Sat, 18 Feb 2006 03:29:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83205-03 for ; Sat, 18 Feb 2006 03:29:03 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by postgresql.org (Postfix) with ESMTP id 8E6429DC88F for ; Sat, 18 Feb 2006 03:29:00 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i6so297972wra for ; Fri, 17 Feb 2006 23:29:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZCEZsenJcUOTQPJ1FZ5TgPmWNJEmdz7MjP5bUB/nwCWEpy4xoyLXCGi4c1txQpNUmoaGGB4w9tRRMOr6fqXTDnsIQRL0Th86QbAex4ZvpCVCBok8kZot+da3o1uxzAMEdvLaf3BVt2x8rg0TpcZyu1nfJoDlRorvwoITBGWnPrc= Received: by 10.65.141.7 with SMTP id t7mr141233qbn; Fri, 17 Feb 2006 23:29:00 -0800 (PST) Received: by 10.65.225.2 with HTTP; Fri, 17 Feb 2006 23:29:00 -0800 (PST) Message-ID: Date: Sat, 18 Feb 2006 03:29:00 -0400 From: "Adam Alkins" To: "Tom Lane" Subject: Re: Index Choice Problem Cc: pgsql-performance@postgresql.org In-Reply-To: <15893.1140241984@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4040_12034139.1140247740907" References: <15893.1140241984@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.14 required=5 tests=[AWL=0.139, HTML_MESSAGE=0.001] X-Spam-Score: 0.14 X-Spam-Level: X-Archive-Number: 200602/305 X-Sequence-Number: 17291 ------=_Part_4040_12034139.1140247740907 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Unfortunately I'm using 8.0.4 and this is for a government website, I only get so many maintenance windows. Is this the only workaround for this issue= ? I did make a test index as you described on my test box and tried the query and it used the new index. However, ORDER BY forum_id then last_post_time i= s simply not the intended sorting order. (Though I'm considering just SELECTing the topic_last_post_time field and resorting the results in the script if this is the only workaround). - Adam On 2/18/06, Tom Lane wrote: > > Adam Alkins writes: > > SELECT t.topic_id > > FROM phpbb_topics AS t > > WHERE t.forum_id =3D 71 > > AND t.topic_id NOT IN (205026, > 29046, 144569, 59780, 187424, > > 138635, 184973, 170551, 22419, 181690, 197254, 205130) > > ORDER BY > t.topic_last_post_time DESC > > LIMIT 23 OFFSET 0 > > If you're using 8.1, you'd probably find that an index on (forum_id, > topic_last_post_time) would work nicely for this. You could use it > in prior versions too, but you'd have to spell the ORDER BY rather > strangely: > ORDER BY forum_id desc, topic_last_post_time desc > The reason for this trickery is to get the planner to realize that > the index order matches the ORDER BY ... > > regards, tom lane > -- Adam Alkins http://www.rasadam.com Mobile: 868-680-4612 ------=_Part_4040_12034139.1140247740907 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Unfortunately I'm using 8.0.4 and this is for a government website, I only = get so many maintenance windows. Is this the only workaround for this issue= ?

I did make a test index as you described on my test box and tried = the query and it used the new index. However, ORDER BY forum_id then last_p= ost_time is simply not the intended sorting order. (Though I'm considering = just SELECTing the topic_last_post_time field and resorting the results in = the script if this is the only workaround).

- Adam

On 2/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adam Alkins <adam.alkins@gmail.= com> writes:
> SELECT t.topic_id
>   &nbs= p;            &= nbsp;      FROM phpbb_topics AS t
> &nb= sp;            =             &nb= sp;    WHERE t.forum_id =3D 71
>          =             &nb= sp;            =     AND t.topic_id NOT IN (205026, 29046, 144569, 59780, 187= 424,
> 138635, 184973, 170551, 22419, 181690, 197254, 205130)
>=             &nb= sp;            =             &nb= sp;         ORDER BY=20 t.topic_last_post_time DESC
>      &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;          LIMIT 23 OFFSET= 0

If you're using 8.1, you'd probably find that an index on (forum_= id,
topic_last_post_time) would work nicely for this.  You cou= ld use it
in prior versions too, but you'd have to spell the ORDER BY rather
s= trangely:
        ORDER BY forum= _id desc, topic_last_post_time desc
The reason for this trickery is to g= et the planner to realize that
the index order matches the ORDER BY ...

           &= nbsp;           &nbs= p;regards, tom lane



--
= Adam Alkins
http://www.rasadam.com
Mobile: 868-680-4612 ------=_Part_4040_12034139.1140247740907--