why "Seq Scan" instead of "Index Scan" ?

  We're using Postgres 7.2.1. I'm trying to do two joins, as shown in
foo1.sql or foo3.sql. One table, "gm_bac_end_match", is indexed on
accession (though that's only part of the primary key.) The other table,
"gm_bacclones", contains two foreign keys, which are accession numbers.

When only one table is JOINed, as in foo2.sql, postgresql uses the index
as I'd hoped. But when I JOIN as in foo1.sql or foo3.sql, it switches to
"Seq Scan", which is much slower.

Can I rewrite the query to persuade the optimizer to use the index?

Would tweaking server parameters help? (I've run VACUUM FULL.)

Thanks,
Josh
-------------------------- the SQL code in question is below

==> foo1.sql <==
drop view foo;
create view foo as
select clone_id,
t7_end.match_start as t7_start,
sp6_end.match_start as sp6_start
from gm_bacclones,
gm_bac_end_match t7_end,
gm_bac_end_match sp6_end
where gm_bacclones.t7_accession = t7_end.accession
and gm_bacclones.sp6_accession = sp6_end.accession;

explain analyze
select *
from foo
where clone_id = '291-19G';


==> foo2.sql <==
drop view foo;
create view foo as
select clone_id,
t7_end.match_start as t7_start
from gm_bacclones,
gm_bac_end_match t7_end
where gm_bacclones.t7_accession = t7_end.accession;

explain analyze
select *
from foo
where clone_id = '291-19G';


==> foo3.sql <==
drop view foo;
create view foo as
select clone_id,
t7_end.match_start as t7_start,
sp6_end.match_start as sp6_start
from (gm_bacclones join
gm_bac_end_match t7_end
on gm_bacclones.t7_accession = t7_end.accession)
join gm_bac_end_match sp6_end
on gm_bacclones.sp6_accession = sp6_end.accession;

explain analyze
select *
from foo
where clone_id = '291-19G';


==> output1.sql <==
DROP
CREATE
NOTICE: QUERY PLAN:

Merge Join (cost=26128.64..26727.05 rows=10109 width=54) (actual
time=16757.05..16757.11 rows=4 loops=1)
-> Sort (cost=392.56..392.56 rows=101 width=38) (actual time=2.70..2.70
rows=2 loops=1)
-> Nested Loop (cost=0.00..389.22 rows=101 width=38) (actual
time=1.10..1.22 rows=2 loops=1)
-> Index Scan using gm_bacclones_pkey on gm_bacclones (cost=0.00..3.01
rows=1 width=22) (actual time=0.48..0.48 rows=1 loops=1)
-> Index Scan using gm_bac_end_match_accession on gm_bac_end_match
sp6_end (cost=0.00..384.92 rows=103 width=16) (actual time=0.60..0.71
rows=2 loops=1)
-> Sort (cost=25736.08..25736.08 rows=188719 width=16) (actual
time=16013.19..16293.32 rows=132619 loops=1)
-> Seq Scan on gm_bac_end_match t7_end (cost=0.00..3943.19 rows=188719
width=16) (actual time=0.44..1060.87 rows=188719 loops=1)
Total runtime: 16933.75 msec

EXPLAIN

==> output2.sql <==
DROP
CREATE
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..389.22 rows=101 width=32) (actual
time=0.89..1.33 rows=2 loops=1)
-> Index Scan using gm_bacclones_pkey on gm_bacclones (cost=0.00..3.01
rows=1 width=16) (actual time=0.40..0.41 rows=1 loops=1)
-> Index Scan using gm_bac_end_match_accession on gm_bac_end_match
t7_end (cost=0.00..384.92 rows=103 width=16) (actual time=0.47..0.90
rows=2 loops=1)
Total runtime: 1.50 msec

EXPLAIN

==> output3.sql <==
DROP
CREATE
NOTICE: QUERY PLAN:

Merge Join (cost=26128.64..26727.05 rows=10109 width=54) (actual
time=16741.18..16741.24 rows=4 loops=1)
-> Sort (cost=392.57..392.57 rows=101 width=38) (actual time=2.87..2.87
rows=2 loops=1)
-> Nested Loop (cost=0.00..389.22 rows=101 width=38) (actual
time=1.16..1.29 rows=2 loops=1)
-> Index Scan using gm_bacclones_pkey on gm_bacclones (cost=0.00..3.01
rows=1 width=22) (actual time=0.50..0.51 rows=1 loops=1)
-> Index Scan using gm_bac_end_match_accession on gm_bac_end_match
t7_end (cost=0.00..384.92 rows=103 width=16) (actual time=0.64..0.76
rows=2 loops=1)
-> Sort (cost=25736.08..25736.08 rows=188719 width=16) (actual
time=15990.74..16276.51 rows=132621 loops=1)
-> Seq Scan on gm_bac_end_match sp6_end (cost=0.00..3943.19 rows=188719
width=16) (actual time=0.46..1055.60 rows=188719 loops=1)
Total runtime: 16754.06 msec

EXPLAIN

--
Josh Burdick
jburdick@gradient.cis.upenn.edu
http://www.cis.upenn.edu/~jburdick