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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
