To: Klint Gore <kg@kgb.une.edu.au>
cc: "Tyrrill, Ed" <tyrrill_ed@emc.com>, pgsql-performance@postgresql.org
Subject: Re: Very slow left outer join 
In-reply-to: <465CD94D319.E8C2KG@129.180.47.120> 
References: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>
	<465CD94D319.E8C2KG@129.180.47.120>
Comments: In-reply-to Klint Gore <kg@kgb.une.edu.au>
	message dated "Wed, 30 May 2007 11:54:21 +1000"
Date: Tue, 29 May 2007 23:18:28 -0400
Message-ID: <26149.1180495108@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Klint Gore <kg@kgb.une.edu.au> writes:
> On Tue, 29 May 2007 17:16:57 -0700, "Tyrrill, Ed" <tyrrill_ed@emc.com> wrote:
>> mdsdb=# explain analyze select backupobjects.record_id from
>> backupobjects left outer join backup_location using(record_id) where
>> backup_id = 1071;

> Why are you using left join?
> The where condition is going to force the row to exist.

Which indeed the planner figured out (note the lack of any mention of
left join in the EXPLAIN result).  Michael put his finger on the problem
though: there's something way off about the rowcount estimate here:

>    ->  Bitmap Heap Scan on backup_location  (cost=3831.20..360207.21
> rows=436915 width=8) (actual time=94.375..97.688 rows=2789 loops=1)
>          Recheck Cond: (backup_id = 1071)
>          ->  Bitmap Index Scan on backup_location_bid
> (cost=0.00..3831.20 rows=436915 width=0) (actual time=84.239..84.239
> rows=2789 loops=1)
>                Index Cond: (backup_id = 1071)

With such a simple index condition the planner really ought to be able
to come close to the right rowcount estimate.  Check for vacuuming
problems, check for lack of ANALYZE, consider whether you need to bump
up the statistics target ...

			regards, tom lane