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

Paul Tomblin wrote:

> Quoting ADBAAMD (adba.amdocs@bell.ca):
>
> 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?

    Probably you are right.

    You could also try other index access methods besides the standard
b-tree.  I don't know about the situation in pgsql, but in Oracle we
have even an index access method create specifically to serve low
selectivity data: bitmaps.

    While I could find a succint description of access methods at
http://www.postgresql.org/users-lounge/docs/7.0/postgres/indices.html, I
don't know how each of them work exactly, nor if any would be any good
for low selectivity situations.  Is there any docs on that?




--
  _
/ \   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