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

Re: Strange left outer join performance issue

From: "Noah M(dot) Daniels" <ndaniels(at)mac(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange left outer join performance issue
Date: 2007-03-23 22:18:34
Message-ID: 797C9FCF-8C8C-4813-8979-68DF7B70FFF8@mac.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)mac(dot)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


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-03-23 22:22:39
Subject: Re: Strange left outer join performance issue
Previous:From: Tom LaneDate: 2007-03-23 22:13:52
Subject: Re: Strange left outer join performance issue

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