Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C5CFB475A71
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 04:10:50 -0500 (EST)
Received: from junk.nocrew.org (junk.nocrew.org [213.242.147.30])
	by postgresql.org (Postfix) with ESMTP id D56794758E6
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 04:10:49 -0500 (EST)
Received: from tomas by junk.nocrew.org with local (Exim 3.35 #1 (Debian))
	for pgsql-sql@postgresql.org
	id 18E4PC-00041P-00; Tue, 19 Nov 2002 10:10:50 +0100
To: pgsql-sql@postgresql.org
Subject: index usage for query
From: Tomas Berndtsson <tomas@nocrew.org>
Date: 19 Nov 2002 10:10:50 +0100
Message-ID: <80adk5sz51.fsf@junk.nocrew.org>
Lines: 88
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/263
X-Sequence-Number: 10405

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