Consider this three-way join that takes the custhist table and joins it to its matching product 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 and then to products. The results will be identical, and the cheapest one will be used.
However, this doesn't have to be the case. Doing these searches for optimal plans is time consuming, and it has the potential to make a bad decision. If you are sure of the right way to join the tables efficiently and want to reduce planning time, you can force the optimizer to use the order you specified when doing a series of explicit JOIN operations. Reduce the join_collapse_limit parameter from its default of 8 to do so; typically the useful value to prevent all join reordering is to lower it to 1. So, this example:
SET join_collapse_limit = 1;
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);
Is going to start with cust_hist, join to products, and then to customers in every case. Alternate plans won't be considered. This can be useful in two main contexts. If query planning time is large for a particular complicated join, discovering the usual order that will be executing and making it explicit in the query can save significant planning time. In cases where the optimizer's selected order was poor, this is a hint you can provide to it on what the right thing to do is.
There is a similar parameter named from_collapse_limit that controls how much freedom the query optimizer has to merge subqueries into upper ones. Generally, both these values are set to the same value, so behavior is easier to predict. There may be cases for setting them to different values if you are being quite careful about tweaking for planning time versus query execution time.