Re: CROSS JOIN performance

Andy Chambers <achambers(at)mcna(dot)net> writes:
> In our porting of a big mysql app to postgres, we're finding lots of
> queries like

> select foo
> from (foo f, bar b)
> left join caz c on f.id = f.caz_id
> where f.id = b.foo_id

> I've seen the message where Tom explains why this is invalid in ANSI
> SQL so I converted it to

> select foo
> from foo f CROSS JOIN bar b
> left join caz c on f.id = f.caz_id
> where f.id = b.foo_id

> ...and it works. However, sometimes quite slowly. When we've looked
> into the slow ones, we've found that changing it again to

> select foo
> from foo f INNER JOIN bar b ON f.id = b.foo_id
> left join caz c on f.id = f.caz_id

> makes it perform much better.

Those formulations look equivalent to me. Could you provide a concrete
test case, or at least some specific queries and their EXPLAIN ANALYZE
results? Which PG version are we talking about? Oh, and are the LEFT
JOIN conditions really not constraining table c at all?

regards, tom lane