From: Tomas Berndtsson <tomas@nocrew.org>
To: pgsql-sql@postgresql.org
Subject: index usage for query
Date: 19 Nov 2002 10:10:50 +0100
Message-ID: <80adk5sz51.fsf@junk.nocrew.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tomas@nocrew.org
Subject: Re: index usage for query
In-Reply-To: <80adk5sz51.fsf@junk.nocrew.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox