Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Tomas Berndtsson <tomas@nocrew.org>, pgsql-sql@postgresql.org
Subject: Re: index usage for query
Date: Tue, 19 Nov 2002 12:25:31 +0000
User-Agent: KMail/1.4.3
References: <80adk5sz51.fsf@junk.nocrew.org>
In-Reply-To: <80adk5sz51.fsf@junk.nocrew.org>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211191225.31051.dev@archonet.com>

On Tuesday 19 Nov 2002 9:10 am, Tomas Berndtsson wrote:
> Hi,
>
> I have a question about index usage in PostgreSQL 7.2.1 on Solaris.

>
> 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=3Ds.element
> AND po.portno=3Ds.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=3D43690.55..43690.55 rows=3D26 width=3D157)
>   ->  Merge Join  (cost=3D41757.20..43689.93 rows=3D26 width=3D157)
>         ->  Index Scan using idx_p_element on port po (cost=3D0.00..72.65
> rows=3D22 width=3D67) ->  Materialize  (cost=3D42682.50..42682.50 rows=3D=
370111
> width=3D90) ->  Merge Join  (cost=3D41757.20..42682.50 rows=3D370111 widt=
h=3D90) ->
>  Sort  (cost=3D41755.93..41755.93 rows=3D370111 width=3D42) ->  Seq Scan =
on
> port_s s (cost=3D0.00..7525.11 rows=3D370111 width=3D42) ->  Sort=20
> (cost=3D1.27..1.27 rows=3D10 width=3D48)
>                           ->  Seq Scan on port_s_p p (cost=3D0.00..1.10
> rows=3D10 width=3D48)

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

Well - the estimate of 370111 rows shows where we have our problem.

An explicit JOIN overrides the planner's normal order of operation. Looking=
 at=20
the plan, I think what's happening is that OUTER JOIN is being done first t=
o=20
"post_s s". Then it does your implicit join.

> 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=3D239.17..239.17 rows=3D26 width=3D157)
>   ->  Merge Join  (cost=3D1.27..238.55 rows=3D26 width=3D157)
>         ->  Nested Loop  (cost=3D0.00..237.19 rows=3D26 width=3D109)
>               ->  Index Scan using idx_p_element on port po
> (cost=3D0.00..72.65 rows=3D22 width=3D67) ->  Index Scan using
> idx_p_s_element_portno on port_s s  (cost=3D0.00..7.38 rows=3D1 width=3D4=
2) ->=20
> Sort  (cost=3D1.27..1.27 rows=3D10 width=3D48)
>               ->  Seq Scan on port_s_p p (cost=3D0.00..1.10 rows=3D10 wid=
th=3D48)

Here you only have one join in two parts "port po" - "port_s s" and then=20
"port_s_p p".

It's the overriding that's hitting you here. If you've got time could you=
=20
rewrite it so that all the joins are implicit and let us know what that doe=
s=20
to the plan?

--=20
  Richard Huxton