The sibling of enable_partitionwise_aggregate, and it shares that parameter’s defining trait: it defaults off, for the same reason. So rather than repeat the off-by-default argument in full, this post covers what’s different — the mechanism, a benefit the aggregate version doesn’t have, and a precondition strict enough to be the main reason the feature doesn’t fire when people

A partitioning optimization, and a notable exception in the enable_* family: it defaults off. Almost every other member of the family defaults on and exists so you can switch a capability off for diagnosis; this one defaults off and exists so you can switch a capability on when you’ve decided it’s worth the cost. That inversion — and the

One of the genuinely important partitioning parameters, and one you should understand rather than merely leave on — because it does its work at two different times, and the difference determines how much it can help your queries. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob. It’s the

A parallel-query toggle that refines the hash join from enable_hashjoin, and it rewards a moment of precision, because “a hash join running in parallel” and “a parallel hash join” are two genuinely different things. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob.

Back to parallel query, and a parameter easy to confuse with the family’s opener. enable_async_append was about running foreign scans concurrently across remote servers. This one is about running local Append children concurrently across worker processes. Different mechanism, different problem, similar-sounding name. Default on, context user, same family framing: a diagnostic instrument, not a tuning knob.

The last of the three join-strategy toggles, and in some ways the most important, because the nested loop is both PostgreSQL’s simplest join and the source of its single most notorious performance disaster. The three algorithms were introduced in enable_hashjoin; this closes the set. Default on, context user, same family framing as enable_async_append: a diagnostic instrument,

The second of the three join-strategy toggles. The three algorithms were laid out in enable_hashjoin — nested loop, merge join, hash join — so here we go deeper on the middle one. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob.

Two enable_* toggles for two plan nodes that both, loosely, cache rows to avoid recomputing them — which is exactly why they get confused, and why they’re worth taking together. They are not variations on one idea. Materialize is a dumb buffer; Memoize is a smart cache. Pinning that difference down is the point of this post. Both default on

The third way to use an index, after the plain index scan and bitmap scan of enable_indexscan and enable_bitmapscan — and the one with the most-misunderstood catch, because an index-only scan can be physically possible and still end up reading the heap on nearly every row. Why that happens is the whole post. Default on, context user, same

A genuinely good feature behind this toggle, which makes it one of the more interesting enable_* switches to understand — and one of the few with a known failure mode worth recognizing. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob. Incremental sort arrived in PostgreSQL 13 (Tomas Vondra and