index usage for query

Hi,

I have a question about index usage in PostgreSQL 7.2.1 on Solaris.

I have three tables like this:

port: element text
portno int
primary key: element, portno
index: element

port_s: element text
portno int
sname text
pri int
primary key: element, portno, sname
index: element
index: element, portno

port_s_p: element text
portno int
sname text
pname text
value text
primary key: element, portno, sname, pname
index: element, portno, sname

At first, I did the query like this:

SELECT po.portno,po.element,s.sname,pri,p.pname,value
FROM port po, port_s s LEFT OUTER JOIN port_s_p p
USING (element, portno, sname) WHERE po.element=s.element
AND po.portno=s.portno AND po.element LIKE 'lab-el1'
ORDER BY po.element,po.portno,pri,s.sname;

And got this query plan using EXPLAIN:

Sort (cost=43690.55..43690.55 rows=26 width=157)
-> Merge Join (cost=41757.20..43689.93 rows=26 width=157)
-> Index Scan using idx_p_element on port po (cost=0.00..72.65 rows=22 width=67)
-> Materialize (cost=42682.50..42682.50 rows=370111 width=90)
-> Merge Join (cost=41757.20..42682.50 rows=370111 width=90)
-> Sort (cost=41755.93..41755.93 rows=370111 width=42)
-> Seq Scan on port_s s (cost=0.00..7525.11 rows=370111 width=42)
-> Sort (cost=1.27..1.27 rows=10 width=48)
-> Seq Scan on port_s_p p (cost=0.00..1.10 rows=10 width=48)

EXPLAIN

What I don't understand is why the index port_s(element,portno)
is not used here.

If I changed the query to:

SELECT po.portno,po.element,s.sname,pri,p.pname,value
FROM port po INNER JOIN port_s s USING (element, portno) LEFT
OUTER JOIN port_s_p p USING (element, portno, sname)
WHERE po.element LIKE 'lab-el1'
ORDER BY po.element,po.portno,pri,s.sname;

I.e. using INNER JOIN instead of the WHERE case to join port and
port_s. This query gave this plan:

NOTICE: QUERY PLAN:

Sort (cost=239.17..239.17 rows=26 width=157)
-> Merge Join (cost=1.27..238.55 rows=26 width=157)
-> Nested Loop (cost=0.00..237.19 rows=26 width=109)
-> Index Scan using idx_p_element on port po (cost=0.00..72.65 rows=22 width=67)
-> Index Scan using idx_p_s_element_portno on port_s s (cost=0.00..7.38 rows=1 width=42)
-> Sort (cost=1.27..1.27 rows=10 width=48)
-> Seq Scan on port_s_p p (cost=0.00..1.10 rows=10 width=48)

EXPLAIN

Can someone explain why the index is used in the second query, but not
in the first?

Greetings,

Tomas