On Sun, 30 Sep 2001, Brian Hirt wrote:
> I have a table with about 1 million rows in it. One of the columns in this
> table is some sort of status (it's an int2). Out of the million rows, only
> about 100 of the rows have a status that is not like the rest.
>
> for example:
> 999,900 have the value 1
> 23 have the value 2
> 67 have the value 3
> 10 have the value 4
>
> I often want to fetch the rows within that subset of 100. When i index this
> column, the planner always seems to choose a table scan when i query it.
> I've tried BTREE and HASH indexes and both do the same thing.
>
> Yes, i do vacuum the table.
>
> Does anyone know how to avoid all these table scans?
Under 7.1 and earlier, you're pretty much stuck with them unless you make
the frequent column value NULL (which has some pain and suffering involved
with queries if you aren't careful). I think 7.2 will handle this better.