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

Paul Tomblin wrote:

> Quoting ADBAAMD (adba(dot)amdocs(at)bell(dot)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(at)bell(dot)ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod(at)amdocs(dot)com