Enable Flags Left Off in Sessions #

Someone set enable_seqscan = off to prove that an index existed and would be used. It worked, the investigation moved on, and the setting went into the application’s connection options because it had seemed to make things faster. Six months later, a table that should be scanned sequentially is read through an index a hundred million times, and nobody connects the two events.

Settings drift generally is covered in session parameter drift, and the precedence that decides which value applies in role and database level setting overrides. This page is about the enable_* family specifically, which does more damage than any other setting when it drifts.

The Condition #

The enable_* flags do not forbid a plan type. They add a very large constant — disable_cost, historically 1.0e10 — to the cost of paths using it, so the planner avoids that node type unless there is no alternative. From PostgreSQL 17 the mechanism is tracked explicitly and reported as a Disabled Nodes count in EXPLAIN output, which makes a stray flag visible in the plan rather than only in the settings.

The family covers scans (enable_seqscan, enable_indexscan, enable_indexonlyscan, enable_bitmapscan, enable_tidscan), joins (enable_hashjoin, enable_mergejoin, enable_nestloop), grouping and sorting (enable_hashagg, enable_sort, enable_incremental_sort, enable_group_by_reordering), and several structural optimisations (enable_material, enable_memoize, enable_partition_pruning, enable_partitionwise_join, enable_partitionwise_aggregate, enable_gathermerge, enable_parallel_hash, enable_presorted_aggregate).

They exist for diagnosis: turning one off shows what the planner would have chosen otherwise, which is how you establish whether a plan was selected on merit or for lack of an alternative. That is a valuable technique and a terrible permanent setting, for three reasons:

enable_partition_pruning = off deserves a particular mention: it is occasionally set to work around a misunderstanding and turns every partitioned query into a full scan of every partition.

Diagnostic use against permanent use As a diagnostic, an enable flag is set for one session, shows what the planner would otherwise choose, identifies the estimate that caused the original choice, and is then reset. Left permanently set, it applies to every query in the session, forces plans the planner does not endorse, hides the estimate problem, and is invisible to anyone reading the query. diagnostic use one session, one investigation reveals the alternative plan identifies the bad estimate reset immediately after left permanently set applies to every query forces unendorsed plans hides the real cause invisible in the SQL the technique is good; the persistence is the problem

Annotated Evidence #

Finding stray flags:

SELECT name, setting, source FROM pg_settings
WHERE name LIKE 'enable\_%' AND setting = 'off';
        name        | setting | source
--------------------+---------+--------
 enable_seqscan     | off     | user
 enable_nestloop    | off     | database
-- 'user' is a role default; 'database' is ALTER DATABASE. Neither is in any file.

The plan it produces:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE placed_at >= '2026-01-01';
Aggregate  (actual time=18402.204..18402.205 rows=1 loops=1)
  ->  Index Scan using orders_placed_at_idx on orders  (actual rows=4102008 loops=1)
        Index Cond: (placed_at >= '2026-01-01'::timestamptz)
        Buffers: shared hit=412008 read=1840220
  Disabled Nodes: 1
-- 4.1 million rows read through an index because sequential scans are penalised
-- "Disabled Nodes" (PostgreSQL 17+) names the cause directly

With the flag reset:

Finalize Aggregate  (actual time=412.204..412.206 rows=1 loops=1)
  ->  Gather  (actual rows=4 loops=1)
        Workers Launched: 3
        ->  Parallel Seq Scan on orders  (actual rows=1025502 loops=4)
              Buffers: shared hit=41204 read=184022
-- 45× faster, and a tenth of the buffers
Three signs of a stray flag Three items are annotated. A Disabled Nodes count in the plan on PostgreSQL 17 and later names the cause directly. An index scan reading millions of rows where a sequential scan would be cheaper is the classic shape. The pg_settings source column identifies the flag as a role or database default rather than a session setting. Disabled Nodes: 1 in the plan a flag is penalising a node Index Scan reading 4.1M rows a sequential scan was cheaper pg_settings: enable_seqscan off, source user a role default before 17, only the settings reveal it

Step-by-Step Resolution #

  1. Check the session as the application sees it with the pg_settings query above, run through the application’s own connection path.

  2. Check the catalogs for role and database defaults: SELECT * FROM pg_db_role_setting;. These survive restarts, deploys and configuration management.

  3. Check the connection string and PGOPTIONS, where an options=-c enable_seqscan=off can hide in plain sight.

  4. Look for Disabled Nodes in captured plans on PostgreSQL 17 and later; it is conclusive and needs no access to the configuration.

  5. Reset them. ALTER ROLE app RESET enable_seqscan; and equivalents. Every one of these should be on in normal operation.

  6. Diagnose the original problem properly. If a sequential scan was genuinely being chosen wrongly, the cause is an estimate or a cost parameter: check row estimates first, then random_page_cost against the storage’s real characteristics, then whether the index is usable at all.

  7. Use SET LOCAL for future investigations, so the flag cannot outlive the transaction that needed it.

How to use one without leaving it behind Four stages. Inside a transaction, set the flag with SET LOCAL. Run EXPLAIN to see the alternative plan. Compare estimated and actual rows to identify why the planner preferred the original. Roll back or commit, which reverts the flag automatically, and fix the estimate. SET LOCAL enable_x inside a transaction EXPLAIN again see what it picks compare estimates find the bad estimate transaction ends flag reverts the fix is at the estimate, never at the flag

Before and After #

-- BEFORE: enable_seqscan = off as a role default
Index Scan using orders_placed_at_idx  rows=4102008  Buffers: 2,252,228   18,402 ms
Disabled Nodes: 1

-- AFTER: ALTER ROLE app RESET enable_seqscan
Parallel Seq Scan on orders  Workers Launched: 3  Buffers: 225,226         412 ms

What to fix instead #

Every legitimate use of an enable_* flag ends with a question about why the planner made the choice it did, and the answers fall into a short list.

A wrong row estimate is the most common by far. If the planner thinks a predicate matches a thousand rows and it matches a million, it will choose a nested loop or an index scan that is correct for a thousand and catastrophic for a million. Extended statistics for correlated columns, expression statistics for expression predicates, and a higher statistics target for skewed columns all address it at the source.

A cost parameter that does not match the hardware is next. random_page_cost at its default of 4.0 describes rotating disks; on SSDs a value between 1.0 and 1.5 is closer to the truth, and leaving it at 4.0 biases every choice towards sequential scans — which is exactly the bias people then try to counteract with enable_seqscan = off. effective_cache_size set far below the machine’s real cache has the same effect.

A missing or unusable index is the third. An index the planner cannot use — wrong column order, a type mismatch on the predicate, a function applied to the column — is invisible to it, and no flag will conjure one.

Fixing any of these produces a plan the planner endorses, which means it will keep endorsing it as the data changes. A forced plan does not have that property.

Common Pitfalls #

Leaving a flag set after an investigation. Every query in the session is distorted. Diagnostic signal: Disabled Nodes in plans, or enable_* off in pg_settings. Fix: reset, and use SET LOCAL next time.

Putting a flag in the connection string. It applies permanently and invisibly. Diagnostic signal: settings that no ALTER explains. Fix: audit options and PGOPTIONS.

Treating a forced plan as a recommendation. It is what the planner picks when denied its preference. Diagnostic signal: a fix that consists of a disabled node type. Fix: address the estimate.

Disabling partition pruning. Every partition is scanned. Diagnostic signal: no Subplans Removed and every partition present at run time. Fix: never set it off outside a test.

Frequently Asked Questions #

What does enable_seqscan = off actually do? #

It adds a very large constant to the cost of sequential scan paths rather than forbidding them, so the planner avoids them unless no alternative exists. From PostgreSQL 17, plans report how many nodes were penalised this way as Disabled Nodes.

Is it safe to leave enable_nestloop off in production? #

No. It applies to every query in the session and forces join methods the planner does not consider best, which turns one fixed query into many broken ones. Fix the estimate that made the nested loop look attractive instead.

How do I find stray enable_* settings? #

Query pg_settings for names like enable_% with setting = ‘off’, through the application’s connection, and check pg_db_role_setting for role and database defaults. On PostgreSQL 17 and later, a Disabled Nodes line in any plan reveals it too.

Up: Session Parameter Drift