Re: sequential joins

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