From: Noah M. Daniels <ndaniels@mac.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange left outer join performance issue
Date: Fri, 23 Mar 2007 18:18:34 -0400
Message-ID: <797C9FCF-8C8C-4813-8979-68DF7B70FFF8@mac.com> (raw)
In-Reply-To: <25685.1174688032@sss.pgh.pa.us>
References: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>
<25685.1174688032@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
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: ndaniels@mac.com, tgl@sss.pgh.pa.us
Subject: Re: Strange left outer join performance issue
In-Reply-To: <797C9FCF-8C8C-4813-8979-68DF7B70FFF8@mac.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