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

Re: Very slow left outer join

From: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
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:54:21
Message-ID: 465CD94D319.E8C2KG@129.180.47.120 (view raw or flat)
Thread:
Lists: pgsql-performance
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;
[...]
> 
> Here are the two tables in the query:
> 
> mdsdb=# \d backup_location
>  Table "public.backup_location"
>   Column   |  Type   | Modifiers
> -----------+---------+-----------
>  record_id | bigint  | not null
>  backup_id | integer | not null
[...]
>  
> mdsdb=# \d backupobjects
>                Table "public.backupobjects"
>      Column     |            Type             | Modifiers
> ----------------+-----------------------------+-----------
>  record_id      | bigint                      | not null
>  dir_record_id  | integer                     |
>  name           | text                        |
>  extension      | character varying(64)       |
>  hash           | character(40)               |
>  mtime          | timestamp without time zone |
>  size           | bigint                      |
>  user_id        | integer                     |
>  group_id       | integer                     |
>  meta_data_hash | character(40)               |

Why are you using left join?

The where condition is going to force the row to exist.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg(at)kgb(dot)une(dot)edu(dot)au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

In response to

Responses

pgsql-performance by date

Next:From: Rajesh Kumar MallahDate: 2007-05-30 02:18:02
Subject: Re: setting up raid10 with more than 4 drives
Previous:From: Michael GlaesemannDate: 2007-05-30 01:38:24
Subject: Re: Very slow left outer join

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