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 multiplereferences to the same table in subselects and
optimizingyour queryappropriately. On occassion, I've had to do this with the same tablesub-selected 50 seperate times
andPostgres handles it OK.
Sometimes you can use a straight LEFT OUTER JOIN instead of asubselect. This depends entirely on whether you are
planningon doingany 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.f2LEFT
OUTERJOIN (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 onthe same table. I recently found this out the
hardway, and crashedan MS SQL Server despite 1gb of memory in the machine. The same querydoes OK in Postgres on less
hardware)
-Josh Berkus
