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)
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