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.
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
