pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Klint Gore <kg@kgb.une.edu.au>
To: Tyrrill, Ed <tyrrill_ed@emc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow left outer join
Date: Wed, 30 May 2007 11:54:21 +1000
Message-ID: <465CD94D319.E8C2KG@129.180.47.120> (raw)
In-Reply-To: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>
References: <A23190A408F7094FAF446C1538222F7603F97F11@avaexch01.avamar.com>

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;
[...]
> 
> 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@kgb.une.edu.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             :                 :
+---------------------------------------+-----------------+



view thread (7+ messages)  latest in thread

Message-ID: <465CD94D319.E8C2KG@129.180.47.120>
Permalink:  ../465CD94D319.E8C2KG@129.180.47.120/
Also on:    postgresql.org/message-id/465CD94D319.E8C2KG@129.180.47.120

 · 

reply
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: kg@kgb.une.edu.au, tyrrill_ed@emc.com
  Subject: Re: Very slow left outer join
  In-Reply-To: <465CD94D319.E8C2KG@129.180.47.120>

* 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