pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Lebedev <olebedev@waterford.org>
Cc: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: sequential joins
Date: Fri, 01 Mar 2002 15:46:41 -0700
Message-ID: <3C8004D1.40DEF8A1@waterford.org> (raw)
References: <web-814232@davinci.ethosmedia.com>

Josh,
the sub-select statement I was using were not for filtering data (in which
case you can move filtering condition in ON part of the join), but rather
renaming the column 'username' with the name I need in the current join
statement. In this case, columns with new names ('artist', 'designer',
'programmer') would be appended to the overall query result and can be
selected by top-level select statement.
This looks like a very clumsy way to do what I need.
Any ideas?
thanks,

Oleg

Josh Berkus wrote:

> Oleg,
>
> > I wonder if there is a better way to do this. Maybe using CASE WHEN
> >  THEN
> > ELSE END clause to avoid multiple scans?
> > thanks,
>
> No, not really.  PostgreSQL is pretty good about detecting multiple
>  references to the same table in subselects and optimizing your query
>  appropriately.  On occassion, I've had to do this with the same table
>  sub-selected 50 seperate times and Postgres handles it OK.
>
> Sometimes you can use a straight LEFT OUTER JOIN instead of a
>  subselect.  This depends entirely on whether you are planning on doing
>  any GROUPing or totals on the main query.  If NOT, then:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
>         LEFT OUTER JOIN (SELECT f2, f3
>                          FROM tableb WHERE f4 = "1") alias1
>                 ON tablea.f1 = alias1.f2
>         LEFT OUTER JOIN (SELECT f2, f3
>                          FROM tableb WHERE f4 = "2") alias2
>                 ON tablea.f1 = alias2.f2;
>
> Is equivalent to:
>
> SELECT tablea.f1, tablea.f2, alias1.f3, alias2.f3
> FROM tablea
>         LEFT OUTER JOIN tableb AS alias1
>                 ON (tablea.f1 = alias1.f2 AND alias1.f4 = "1")
>         LEFT OUTER JOIN tableb AS alias2
>                 ON (tablea.f1 = alias2.f2 AND alias2.f4 = "2") ;
>
> And the second should run a bit faster.
>
> (FYI:  MS SQL Server 7.0 does *not* optimize for multiple subselects on
>  the same table.  I recently found this out the hard way, and crashed
>  an MS SQL Server despite 1gb of memory in the machine.  The same query
>  does OK in Postgres on less hardware)
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




view thread (6+ messages)  latest in thread

Message-ID: <3C8004D1.40DEF8A1@waterford.org>
Permalink:  ../3C8004D1.40DEF8A1@waterford.org/
Also on:    postgresql.org/message-id/3C8004D1.40DEF8A1@waterford.org

 · 

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-sql@postgresql.org
  Cc: olebedev@waterford.org
  Subject: Re: sequential joins
  In-Reply-To: <3C8004D1.40DEF8A1@waterford.org>

* 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