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.