from_collapse_limit — PostgreSQL configuration parameter

The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items.

At a glance

What it does

The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but might yield inferior query plans. The default is eight. For more information see explicit_joins.

Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in non-optimal plans. See runtime_config_query_geqo.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW from_collapse_limit; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'from_collapse_limit';.

Tuning guidance

Raise it (e.g. 12-16) to let the planner consider better join orders across many tables, at the cost of longer planning; lower it to cap planning cost on very wide joins.

Reference

PostgreSQL documentation — from_collapse_limit.