Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id A511447596C for ; Fri, 1 Mar 2002 15:28:47 -0500 (EST) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 814232; Fri, 01 Mar 2002 12:28:46 -0800 From: "Josh Berkus" Subject: Re: sequential joins To: Oleg Lebedev , Postgres SQL Mailing List X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Fri, 01 Mar 2002 12:28:46 -0800 Message-ID: In-Reply-To: <3C7FE2DF.2AF0A2BC@waterford.org> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/13 X-Sequence-Number: 6895 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