From: Tom Lane <tgl@sss.pgh.pa.us>
To: Klint Gore <kg@kgb.une.edu.au>
Cc: Tyrrill, Ed <tyrrill_ed@emc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow left outer join
Date: Tue, 29 May 2007 23:18:28 -0400
Message-ID: <26149.1180495108@sss.pgh.pa.us> (raw)
In-Reply-To: <465CD94D319.E8C2KG@129.180.47.120>
References: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>
<465CD94D319.E8C2KG@129.180.47.120>
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
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, kg@kgb.une.edu.au, tyrrill_ed@emc.com
Subject: Re: Very slow left outer join
In-Reply-To: <26149.1180495108@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