Re: Very slow left outer join

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             :                 :
+---------------------------------------+-----------------+