From: Richard Huxton <dev@archonet.com>
To: Tomas Berndtsson <tomas@nocrew.org>
To: pgsql-sql@postgresql.org
Subject: Re: index usage for query
Date: Tue, 19 Nov 2002 12:25:31 +0000
Message-ID: <200211191225.31051.dev@archonet.com> (raw)
In-Reply-To: <80adk5sz51.fsf@junk.nocrew.org>
References: <80adk5sz51.fsf@junk.nocrew.org>
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=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)
> 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
the plan, I think what's happening is that OUTER JOIN is being done first to
"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=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)
Here you only have one join in two parts "port po" - "port_s s" and then
"port_s_p p".
It's the overriding that's hitting you here. If you've got time could you
rewrite it so that all the joins are implicit and let us know what that does
to the plan?
--
Richard Huxton
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: dev@archonet.com, tomas@nocrew.org
Subject: Re: index usage for query
In-Reply-To: <200211191225.31051.dev@archonet.com>
* 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