From: Richard Huxton <dev@archonet.com>
To: George Woodring <george.woodring@iglass.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with nested loop left join performance
Date: Wed, 22 Feb 2006 09:38:04 +0000
Message-ID: <43FC30FC.3080800@archonet.com> (raw)
In-Reply-To: <1B1B254441DB31448BD34C5BD73B0B8B28FBF6@PHOENIX.istructure.com>
References: <1B1B254441DB31448BD34C5BD73B0B8B28FBF6@PHOENIX.istructure.com>
George Woodring wrote:
>
> explain analyze SELECT column1, column2, column3, column4, column5,
> column6, column7, column8 FROM (SELECT CASE status WHEN 0 THEN 0 WHEN 1
> THEN 1 ELSE -1 END AS column1, mac AS column2, account AS column3,
> number || ' ' || address AS column4, 'qmod' || '.' || 'dmod' AS column5,
> node AS column6, grid AS column7, boxtype AS column8, number, address
> FROM settop_billing LEFT OUTER JOIN (dhct JOIN dhct_davic USING(mac))
> USING (mac) WHERE region='GTown1E' AND node='1E012' ) AS foo;
Ach y fi! Let's format that a bit better, eh?
explain analyze
SELECT column1, column2, column3, column4, column5,column6, column7, column8
FROM (
SELECT
CASE status WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END AS column1,
mac AS column2,
account AS column3,
number || ' ' || address AS column4,
'qmod' || '.' || 'dmod' AS column5,
node AS column6,
grid AS column7,
boxtype AS column8,
number,
address
FROM
settop_billing
LEFT OUTER JOIN
(dhct JOIN dhct_davic USING(mac))
USING
(mac)
WHERE
region='GTown1E' AND node='1E012'
) AS foo;
Now we can see what's happening. Well, looking at it laid out like that,
I'm suspcious of the (dhct JOIN dhct_davic) on the outside of an outer
join. Looking at your explain we do indeed have two sequential scans
over the tables in question - the big one being dhct...
> -> Append (cost=0.00..8881.11 rows=384912 width=8)
> (actual time=0.023..10914.365 rows=384900 loops=1)
> -> Seq Scan on dhct_davic (cost=0.00..0.00 rows=1
> width=8) (actual time=0.002..0.002 rows=0 loops=1)
> -> Seq Scan on probe_dhct_davic dhct_davic
> (cost=0.00..8881.11 rows=384911 width=8) (actual time=0.018..10505.255
> rows=384900 loops=1)
> -> Hash (cost=12154.13..12154.13 rows=410613 width=6)
> (actual time=923.433..923.433 rows=0 loops=1)
> -> Seq Scan on dhct (cost=0.00..12154.13
> rows=410613 width=6) (actual time=0.019..534.641 rows=409576 loops=1)
With 7.4 I seem to remember that explicit JOINs force the evaluation
order, but I'm not if even later versions will rewrite your query. It's
too early in the morning for me to figure out if it's safe in all cases.
Anyway, for your purposes, I'd say something more like:
FROM settop_billing LEFT JOIN dhct LEFT JOIN dhct_davic
That should let the planner do the joins in a more reasonable order.
--
Richard Huxton
Archonet Ltd
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-performance@postgresql.org
Cc: dev@archonet.com, george.woodring@iglass.net
Subject: Re: Help with nested loop left join performance
In-Reply-To: <43FC30FC.3080800@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