PostgreSQL 10 High Performance - Third Edition

Forcing join order

Consider this three-way join that takes the cust_hist table and joins it to its matching products and customer keys:

SELECT * FROM cust_hist h INNER JOIN products p ON (h.prod_id=p.prod_id) INNER JOIN customers c ON (h.customerid=c.customerid);  

This join is identical to an implementation that uses an implicit join and a WHERE clause:

SELECT * FROM cust_hist h,products p,customers c  WHERE h.prod_id=p.prod_id AND h.customerid=c.customerid;  

In either case, the query optimizer is free to choose plans that execute these joins in several orders. It could join cust_hist to products, then to customers, or it could join to customers, then to products. The results will be identical, and the cheapest one will be used.

However, this ...