Re: Strange left outer join performance issue

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