Skip site navigation (1) Skip section navigation (2)

Re: Index Choice Problem

From: "Adam Alkins" <adam(dot)alkins(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index Choice Problem
Date: 2006-02-18 07:29:00
Message-ID: e5edd73e0602172329p15cf80b4o75aa3e99d0d7fafe@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Adam Alkins <adam(dot)alkins(at)gmail(dot)com> writes:
> > SELECT t.topic_id
> >                       FROM phpbb_topics AS t
> >                               WHERE t.forum_id = 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

In response to

Responses

pgsql-performance by date

Next:From: Adam AlkinsDate: 2006-02-18 07:39:19
Subject: Re: Index Choice Problem
Previous:From: Tom LaneDate: 2006-02-18 05:53:04
Subject: Re: Index Choice Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group