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

Quoting ADBAAMD (adba(dot)amdocs(at)bell(dot)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(at)xcski(dot)com>, not speaking for anybody
Every program has two purposes -- one for which it was written and
another for which it wasn't.