From: Michael Glaesemann <grzm@seespotcode.net>
To: Tyrrill, Ed <tyrrill_ed@emc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow left outer join
Date: Tue, 29 May 2007 20:38:24 -0500
Message-ID: <D140B279-DAD1-479C-9E5B-557D575D9CC4@seespotcode.net> (raw)
In-Reply-To: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>
References: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>
On May 29, 2007, at 19:16 , Tyrrill, Ed wrote:
> -----
> Hash Join (cost=361299.50..1054312.92 rows=34805 width=8) (actual
> time=1446.861..368723.597 rows=2789 loops=1)
> Hash Cond: ("outer".record_id = "inner".record_id)
> -> Seq Scan on backupobjects (cost=0.00..429929.79 rows=13136779
> width=8) (actual time=5.165..359168.216 rows=13136779 loops=1)
> -> Hash (cost=360207.21..360207.21 rows=436915 width=8) (actual
> time=820.979..820.979 rows=2789 loops=1)
> -> Bitmap Heap Scan on backup_location
> (cost=3831.20..360207.21 rows=436915 width=8) (actual
> time=797.463..818.269 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=59.592..59.592
> rows=2789 loops=1)
Off the cuff, when was the last time you vacuumed or ran ANALYZE?
Your row estimates look off by a couple orders of magnitude. With up-
to-date statistics the planner might do a better job.
As for any other improvements, I'll leave that to those that know
more than I. :)
Michael Glaesemann
grzm seespotcode net
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: grzm@seespotcode.net, tyrrill_ed@emc.com
Subject: Re: Very slow left outer join
In-Reply-To: <D140B279-DAD1-479C-9E5B-557D575D9CC4@seespotcode.net>
References: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com> <D140B279-DAD1-479C-9E5B-557D575D9CC4@seespotcode.net>
* 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