From: Josh Berkus <josh@agliodbs.com>
To: Oleg Lebedev <olebedev@waterford.org>
To: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: sequential joins
Date: Fri, 01 Mar 2002 12:28:46 -0800
Message-ID: <web-814232@davinci.ethosmedia.com> (raw)
In-Reply-To: <3C7FE2DF.2AF0A2BC@waterford.org>
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
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: josh@agliodbs.com, olebedev@waterford.org
Subject: Re: sequential joins
In-Reply-To: <web-814232@davinci.ethosmedia.com>
* 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