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

Quoting ADBAAMD (adba.amdocs@bell.ca):
> Paul Tomblin wrote:
> > If I try
> >     explain select * from waypoint where country = 'BELIZE';
> > a query that will only select one record out of the 8300-odd, it still
> > doesn't use the index.
> >     Seq Scan on waypoint  (cost=455.13 rows=6813 width=130)
>
>     Selectivity isn't about specific values, but about averages.
>
>     If the planner would know statistics about each and every indexed value
> on the database, it would take a lot of effort to ANALYZE indexed data,
> the memory and disk consumption by statistics would be high, and CPU
> usage by the planner would go gaga.  So it analyzes just averages.
>
>     It doesn't matter that BELIZE has a high selectivity, but that country
> has a low one.

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.

I think I understand now.  If this is correct, then doesn't it make sense
just to drop that index?  At least until I get a lot more data from other
countries?

waypoint=> select count(*), country from waypoint group by country;
count|country
-----+--------------------
    2|ANTIGUA AND BARBUDA
   15|BAHAMAS
    1|BARBADOS
    1|BELIZE
  741|CANADA
    1|CAYMAN ISLANDS
    5|COLOMBIA
    2|COSTA RICA
   23|CUBA
    1|DOMINICA
    3|DOMINICAN REPUBLIC
    1|ECUADOR
    3|FED STS MICRONESIA
    4|FRENCH WEST INDIES
    1|GRENADA
    1|GUYANA
    2|HAITI
    2|HONDURAS
    4|JAMAICA
    2|MARSHALL ISLANDS
   31|MEXICO
    3|NETHERLANDS ANTILLES
    2|NICARAGUA
    1|PALAU
    8|PANAMA
    2|TRINIDAD AND TOBAGO
    2|TRUST TERRITORIES
    2|TURKS AND CAICOS ISL
 7436|USA
    5|VENEZUELA
(30 rows)

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Every program has two purposes -- one for which it was written and
another for which it wasn't.