Mastering PostgreSQL 9.6

Understanding the join_collapse_limit variable

During the planning process, PostgreSQL tries to check all possible join orders. In many cases, this can be pretty expensive because there can be many permutations, which naturally slows down the planning process. The join_collapse_limit variable is here to give the developer a tool to actually work around these problems and define, in a more straightforward way, how a query should be processed.

To show what this setting is all about, I have compiled a little example:

SELECT   *  FROM     tab1, tab2, tab3 WHERE    tab1.id = tab2.id           AND tab2.ref = tab3.id; SELECT   *  FROM     tab1 CROSS JOIN tab2           CROSS JOIN tab3  WHERE    tab1.id = tab2.id           AND tab2.ref = tab3.id; SELECT   *  FROM     tab1 JOIN (tab2 JOIN tab3   ON (tab2.ref ...