Re: quickly getting the top N rows

On Thu, 4 Oct 2007, Bill Moran wrote:

> However, 2 guesses:
> 1) You never analyzed the table, thus PG has awful statistics and
>   doesn't know how to pick a good plan.
> 2) You have so few rows in the table that a seq scan is actually
>   faster than an index scan, which is why PG uses it instead.

No, the tables are recently analyzed and there are a couple hundred
thousand rows in there. But I think I just figured it out.... it's a
3-column index, and two columns of that index are the same for every row.
When I drop those two columns from the ordering restriction, the index
gets used and things speed up 5 orders of magnitude.

Maybe the planner is smart enough to think that if a column in the order
by clause is identical for most rows, then using an index won't help....
but not smart enough to realize that if said column is at the *end* of the
order by arguments, after columns which do sort quite well, then it should
use an index after all.