Re: optimizer bent on full table scan

Greg Stark <gsstark@mit.edu> writes:
> I have a strange problem with the optimizer from CVS checked out as of about a
> week ago. It seems to be insisting on using a full table scan for a table
> lookup where the full table scan is about 20x slower than an index lookup.

> I think somehow it's being confused by the fact that some values of the index
> are populated and others haven't been so the distribution is odd.

Well, it's doing a heck of a good job of estimating the number of
matching rows --- can't complain about 8757 vs 8721.  So there's some
other failure of modeling here.  The only idea that comes to mind is
that maybe the rows matching foobar_id = 900 are tightly clustered in
the table, so that the planner's assumption of random fetches is overly
pessimistic.  But the small correlation value says that there's not much
overall structure in the table's ordering.  Can you shed any light on
that?

            regards, tom lane