Guillaume Lelarge <guillaume@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
