Re: why is the LIMIT clause slowing down this SELECT?

On Wed, 2007-08-01 at 21:42 -0500, Mason Hale wrote:
> The score in this case are definitely not a normal distribution. They
> follow a power law pattern, with a few with very high scores and a
> long tail.
>
> I ended up coercing it to use plan 2 by dropping the index on topic_feed(score).
>

I think Tom had the correct advice, you should try an index on
(topic_id,score).

> Which raises another question -- if the planner has already used an
> index on topic_id to select the rows, would it ever us another index
> on score to order the rows? Or is a compound topic_feed(topic_id,
> score) index the way to go there?
>

Two indexes can only be combined for a bitmap index scan, and a bitmap
is in heap order, not index order. That means additional indexes only
help to do additional filtering before it tries to fetch from the table
itself. In your case there is no filter on "score" at all, "score" is
just a sort order.

A compound index should give you what you want.

Regards,
Jeff Davis