Skip site navigation (1) Skip section navigation (2)

Re: Very slow left outer join

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
Cc: "Tyrrill, Ed" <tyrrill_ed(at)emc(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very slow left outer join
Date: 2007-05-30 03:18:28
Message-ID: 26149.1180495108@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> writes:
> On Tue, 29 May 2007 17:16:57 -0700, "Tyrrill, Ed" <tyrrill_ed(at)emc(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2007-05-30 03:26:37
Subject: Re: setting up raid10 with more than 4 drives
Previous:From: Rajesh Kumar MallahDate: 2007-05-30 02:18:02
Subject: Re: setting up raid10 with more than 4 drives

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group