Re: help understanding explain output

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Le 15/02/2011 15:49, Luca Ferrari a �crit :
>> So a sequential scan. I know that the optimizer will not consider an index if
>> it is not filtering, but I don't understand exactly why in this case.

> Accessing a page in an index is way costier then accessing a page in an
> table with a sequential scan. By default, random_page_cost is 4 times
> seq_page_cost. So it's not really surprising that when you want to get
> half the table, PostgreSQL won't use the index. You would need to have a
> really selective query to make an index scan interesting to use.

As a rough rule of thumb, a regular indexscan is useful when the query
selects not more than about 1% of rows, while a bitmap indexscan is
useful up to about 10% of the table.  More than that, a seqscan is the
right thing to use.  If the table's row ordering is very well correlated
with the index, or if you've reduced random_page_cost, the cutoff
percentages are higher.  But in no case is it likely to be a win to use
an index to fetch half of a table.

(BTW, in the given test case, the reason the planner isn't using the
index even with seqscan off is that it *can't*.  You got the WHERE
condition backwards.)

            regards, tom lane