Handling Large Joins with Subqueries in PostgreSQL
Subqueries offer a notable advantage in PostgreSQL: they can shrink the join tree search space. With join_collapse_limit = 12, a query with 20 joins can be split into smaller segments. This lets PostgreSQL choose an optimal join order within each segment, rather than mechanically following the SQL order from the source text.
This approach raises concerns about how effectively PostgreSQL transforms subqueries into joins. One open question is whether large-scale deployments have documented cases where such transformations hurt performance—specifically, when subquery flattening (including correlated subqueries or initplans) goes beyond the collapse limit and causes slower execution.
