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.