Re: index skipped in favor of seq scan.

ryan(dot)a(dot)roemmich(at)mail(dot)sprint(dot)com writes:
> On the other end of the spectrum there are many addresses with only one
> entry. When I use one of these addresses in the WHERE clause it takes
> just as long as the address with 150k rows. If the sequential scan is
> better for 150k rows out of 800k rows, what about 1 out of 800k? It
> seems that when my table grew to this size the index was no longer used.

The problem is that the 150k-duplicates value is dominating the
planner's rather inadequate statistics, and causing it to believe that
the table contains only a few values that all occur many times. If that
were the true scenario then the use of seq scan would be the correct
choice.

This is fixed (I hope) for 7.2, but there's not much to be done about
it in current releases, unless you can avoid storing the 150k-duplicates
value. Is that a real value, or just a dummy? If you could replace it
with NULL then the right things would happen, because the statistics do
already distinguish NULL from regular data values.

regards, tom lane