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

Paul Tomblin wrote:

> Quoting ADBAAMD (adba.amdocs@bell.ca):
>
>> Paul Tomblin wrote:
>>
>>> I have a table with columns 'country' and 'state'.  I put indexes on both
>>> of them.  I've done the "vacuum analyze" as per the faq.  But when I ask
>>> it to explain, it says it will use the index on 'state' if I do a
>>>     select * from waypoint where state = 'ON';
>>> but it won't use the index on 'country' if I do a
>>>     select * from waypoint where country = 'CANADA';
>>
>>     Maybe it's just my Oracle side, but doesn't country has a too low
>> selectivity?
>
>
> 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.



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com