Skip site navigation (1) Skip section navigation (2)

Re: Help with nested loop left join performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: George Woodring <george(dot)woodring(at)iglass(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Help with nested loop left join performance
Date: 2006-02-22 15:04:05
Message-ID: 11673.1140620645@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Richard Huxton <dev(at)archonet(dot)com> writes:
> George Woodring wrote:
>>      FROM
>>          settop_billing
>>      LEFT OUTER JOIN
>>          (dhct JOIN dhct_davic USING(mac))
>>      USING
>>          (mac)
>>      WHERE
>>          region='GTown1E' AND node='1E012'

> 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.

CVS HEAD can re-order left joins in common cases, but no existing
release will touch the ordering of outer joins at all.

It's impossible to tell here which tables the WHERE-clause restrictions
actually bear on, so there's no way to say whether a different join
order would help.  My guess though is that George may be stuck --- in
general you can't move a join into or out of the right side of a left
join without changing the answers.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Brendan DuddridgeDate: 2006-02-22 15:48:43
Subject: Re: LIKE query on indexes
Previous:From: Richard HuxtonDate: 2006-02-22 14:22:01
Subject: Re: --pls reply ASAP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group