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

I would guess that the optimizer is choosing a sequential scan when
the country is CANADA because the number of rows fetched as a
percentage of total rows would warrant it. For example, country =
'CANADA' might be true for %30 of the total rows whereas STATE = 'ON'
might only represent 2%, and thus the index scan.

The EXPLAIN shows estimates and an excellent explanation of them can
be found here:

http://www.postgresql.org/users-lounge/docs/7.0/user/c4884.htm#AEN
4889

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Paul Tomblin [SMTP:ptomblin@xcski.com]
Sent:    Sunday, April 01, 2001 3:39 PM
To:    pgsql-general@postgresql.org
Subject:    [GENERAL] Ok, why isn't it using *this* index?

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';

Some other interesting things are that it uses the index on state
even if
I say "where state in ('ON','QC','BC','AB')", and it uses the index
on
state but not the one on country if I combine "where state = 'ON' and
country = 'CANADA'".

Here's what it says:
    waypoint=> explain select * from waypoint where state = 'ON';
    NOTICE:  QUERY PLAN:

    Index Scan using waypoint_state on waypoint  (cost=7.17 rows=84
    width=130)

    EXPLAIN
    waypoint=> explain select * from waypoint where country =
'CANADA';
    NOTICE:  QUERY PLAN:

    Seq Scan on waypoint  (cost=455.13 rows=6813 width=130)

    EXPLAIN

Also, can anybody explain why the "rows=" doesn't correspond to
anything
logical?  For instance, in the first one it says "rows=84" even
though
there are 107 matching records, and 71 different states.


--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Diplomacy is the ability to let someone else have your way.