Re: why am I getting a seq scan on this query?

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Someone might have a better idea but my guess is that PG things the
> seq_scan would be faster.

That's what it thinks, and it might be right. This query is fetching 2%
of the table, which is near the crossover point where a seqscan is
faster, assuming that the rows aren't very wide and the target rows are
fairly randomly distributed through the table's pages.

> You could try decreasing your random_page_cost.

First thing to do is force the plan choice (set enable_seqscan = off)
and see what timings you actually get each way. If the planner really
is guessing materially wrong, then adjusting the cost parameters is
called for. Don't set them on the basis of a single test case though...

BTW, the bitmap indexscan method available in PG 8.1 can do a lot better
than plain indexscan for scenarios like this, so updating to 8.1 might
be a good answer too.

regards, tom lane