Re: Ok, why isn't it using *this* index?

Paul Tomblin <ptomblin@xcski.com> writes:
> Ok, so if I understand you correctly, the fact that about 90% of the
> records have country='USA' and about 9% of the records have
> country='CANADA' means that it's never going to use the index because it
> on average, a query is going to be for USA, and a sequential scan is going
> to be better.

Actually, 7.0 and later (which you are not using, I gather from your
EXPLAIN display) do know the difference between the most common value
in the column and the rest of 'em.  I think that 7.0 would choose an
indexscan in the case where it can see that you are not looking for
'USA'.  Which would be the right choice for 'BELIZE', but probably
not the right choice for 'CANADA'.

For 7.2 I am hoping to extend the stored stats to know about the top
three or so common values, not just one, so that we can deal more
effectively with data distributions like this one.  But in any case,
most of the respondents in this thread have been assuming that you
were running a reasonably current Postgres.  Try upgrading ...

            regards, tom lane