pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Noah M. Daniels <ndaniels@mac.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange left outer join performance issue
Date: Fri, 23 Mar 2007 18:13:52 -0400
Message-ID: <25685.1174688032@sss.pgh.pa.us> (raw)
In-Reply-To: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>
References: <5335C295-4D3E-4A1B-A270-6B822377DD25@mac.com>

"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



view thread (6+ messages)  latest in thread

Message-ID: <25685.1174688032@sss.pgh.pa.us>
Permalink:  ../25685.1174688032@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25685.1174688032@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, ndaniels@mac.com
  Subject: Re: Strange left outer join performance issue
  In-Reply-To: <25685.1174688032@sss.pgh.pa.us>

* 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