Re: Left Join Performance vs Inner Join Performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dave Dutcher" <dave(at)tridecap(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Left Join Performance vs Inner Join Performance
Date: 2006-01-11 04:38:20
Message-ID: 8321.1136954300@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Dave Dutcher" <dave(at)tridecap(dot)com> writes:
> I have an inner join query that runs fast, but I when I change to a left
> join the query runs 96 times slower.

This looks like an issue that is fixed in the latest set of releases,
namely that OUTER JOIN ON conditions that reference only the inner
side of the join weren't getting pushed down into indexquals. See
thread here:
http://archives.postgresql.org/pgsql-performance/2005-12/msg00134.php
and patches in this and the following messages:
http://archives.postgresql.org/pgsql-committers/2005-12/msg00105.php

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Creager 2006-01-11 05:10:55 Re: Index isn't used during a join.
Previous Message David Lang 2006-01-11 03:50:46 Re: How to handle a large DB and simultaneous accesses?