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

Re: Very slow left outer join

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: "Tyrrill, Ed" <tyrrill_ed(at)emc(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very slow left outer join
Date: 2007-05-30 01:38:24
Message-ID: D140B279-DAD1-479C-9E5B-557D575D9CC4@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-performance
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



In response to

Responses

pgsql-performance by date

Next:From: Klint GoreDate: 2007-05-30 01:54:21
Subject: Re: Very slow left outer join
Previous:From: Craig JamesDate: 2007-05-30 01:33:48
Subject: Re: How PostgreSQL handles multiple DDBB instances?

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