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)       ->
IndexScan using idx_p_element on port po (cost=0.00..72.65 rows=22 width=67)       ->  Materialize
(cost=42682.50..42682.50rows=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
Scanon 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=22width=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