In-Reply-To: <25685.1174688032@sss.pgh.pa.us>
References: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>
	<25685.1174688032@sss.pgh.pa.us>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <797C9FCF-8C8C-4813-8979-68DF7B70FFF8@mac.com>
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: 7bit
From: "Noah M. Daniels" <ndaniels@mac.com>
Subject: Re: Strange left outer join performance issue
Date: Fri, 23 Mar 2007 18:18:34 -0400
To: Tom Lane <tgl@sss.pgh.pa.us>

Tom,

You're right; this is postgres 8.0.8. Perhaps upgrading will solve  
this issue. Is there any way to get this query to perform better in  
postgres 8.0.8?

thanks!

On Mar 23, 2007, at 6:13 PM, Tom Lane wrote:

> "Noah M. Daniels" <ndaniels@mac.com> writes:
>> I have two queries that are very similar, that run on the same table
>> with slightly different conditions. However, despite a similar number
>> of rows returned, the query planner is insisting on a different
>> ordering and different join algorithm, causing a huge performance
>> hit. I'm not sure why the planner is doing the merge join the way it
>> is in the slow case, rather than following a similar plan to the fast
>> case.
>
> It likes the merge join because it predicts (apparently correctly)  
> that
> only about 1/14th of the table will need to be scanned.  This'd be an
> artifact of the relative ranges of supplier ids in the two tables.
>
> What PG version is this?  8.2 understands about repeated indexscans
> being cheaper than standalone ones, but I get the impression from the
> explain estimates that you may be using something older that's
> overestimating the cost of the nestloop way.
>
> 			regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings