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(at)mascari(dot)com

-----Original Message-----
From: Paul Tomblin [SMTP:ptomblin(at)xcski(dot)com]
Sent: Sunday, April 01, 2001 3:39 PM
To: pgsql-general(at)postgresql(dot)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(at)xcski(dot)com>, not speaking for anybody
Diplomacy is the ability to let someone else have your way.