Are implicit joins as efficient as explicit joins in Postgres?

Question

It's often convenient to write:

SELECT * 
FROM t1   # ... +many more tables
INNER JOIN t2 ON (t1.id = t2.col)
INNER JOIN t3 ON (t1.id = t3.col)
INNER JOIN t4 ON (t1.id = t4.col)
...

as a cross join with conditionals:

SELECT * 
FROM t1, t2, t3, t4   # ... +many more tables
WHERE
       t1.id = t2.col
   AND t1.id = t3.col
   AND t1.id = t4.col
   # +include matches on columns of other tables

However, a naive implementation of a cross join would have a higher time complexity than the inner join. Does Postgres optimize the second query into one with the same time complexity as the first?

Answer 1

I would use explicit JOIN syntax, but reduce the noise:

SELECT *  -- really? *all* columns?
FROM   t1
JOIN   t2 ON t1.id = t2.col
JOIN   t3 ON t1.id = t3.col
JOIN   t4 ON t1.id = t4.col
-- ... more tables
WHERE ...

Place conditions linking two tables in the JOIN clause.
Place other conditions in the WHERE clause.

INNER is a noise word. Parentheses are not required around join conditions.

To answer your question:

Are implicit joins as efficient as explicit joins in Postgres?

Yes. There is no difference in efficiency. Postgres is typically free to rearrange the order of join operations and apply JOIN and WHERE conditions in any order it sees fit. The manual:

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not constrain the join order.

There are special considerations for OUTER joins that do not apply here.
And be aware of some limitations:

• Explicit joins bind stronger than implicit joins. This can have side effects when mixing both - which you should avoid unless you understand the implications. See:

• Why does this implicit join get planned differently than an explicit join?

• What does [FROM x, y] mean in Postgres?

• With more than join_collapse_limit items in the FROM list (default is currently 8), Postgres switches from an exhaustive search for the best query plan to generic methods. Hence the order of explicit join items becomes yet more important - or even instructive.

Since you mentioned:

+many more tables

... you probably need to consider how to optimize planning time and query execution time by carefully choosing the order of explicit joins and placement of conditions. You may want to use subqueries, CTEs, mix explicit and implicit joins, use parentheses among groups of FROM clause items or play with configuration settings to get best results.

Related:

• Postgres JOIN conditions vs WHERE conditions
• Complex view becomes slow when adding ORDER BY in outer query with small LIMIT