Loading feed…
Planet PostgreSQL0 Hot0 comments4 min read
All Your GUCs in a Row: join_collapse_limit
PostgreSQL's join_collapse_limit is the closest thing the database has to a real join-order hint. Set to its default of 8, it controls when the planner flattens explicit JOIN constructs into a single reorderable list versus leaving them nested as written. Setting it to 1 with SET LOCAL inside a transaction forces the planner to join tables in exactly the written order, which explains why queries with more than eight explicit joins (often from ORM-generated SQL or stacked views) can suddenly fall off a performance cliff once a ninth join is added. Raising the limit fixes that but costs planning time, and pushing it past geqo_threshold (default 12) hands planning to the GEQO random-sampling algorithm, so both should be moved together. The piece frames forcing join order as a tourniquet rather than a cure: the durable fix is improving statistics via higher statistics targets, CREATE STATISTICS, and ANALYZE.
Source: https://postgr.es/p/9sF. 8 Sync News only summarizes and links out; content copyright belongs to the authors and original sources.
