From: Adam Alkins <adam.alkins@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index Choice Problem
Date: Sat, 18 Feb 2006 03:29:00 -0400
Message-ID: <e5edd73e0602172329p15cf80b4o75aa3e99d0d7fafe@mail.gmail.com> (raw)
In-Reply-To: <15893.1140241984@sss.pgh.pa.us>
References: <e5edd73e0602160903y16a0bd3ao2a0dd817fdf72cc@mail.gmail.com>
<15893.1140241984@sss.pgh.pa.us>
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@sss.pgh.pa.us> wrote:
>
> Adam Alkins <adam.alkins@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adam.alkins@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Index Choice Problem
In-Reply-To: <e5edd73e0602172329p15cf80b4o75aa3e99d0d7fafe@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox