In PostgreSQL, what does SET enable_seqscan = off actually do, and why is it a diagnostic test rather than a fix?
answer
- discourages, does not forbid
- time both plans for real
- one transaction, never global
- 18 counts disabled nodes
basics
~20 sIt makes the planner avoid sequential scans unless no other path exists, exposing the plan it rejected so you can time it. A faster forced plan proves the estimates or cost settings are wrong; fix those, not the switch.
solid answer
~50 s`enable_seqscan` is one of PostgreSQL's `enable_*` planner switches. Turning it off does not forbid sequential scans; it marks them as disabled so the planner takes any alternative first. Since PostgreSQL 18 the planner compares the number of disabled nodes before cost, and `EXPLAIN` prints `Disabled: true` on a node it had to use anyway; older releases added a very large cost penalty instead. I run it inside a transaction with `SET LOCAL`, run `EXPLAIN (ANALYZE)` both ways and compare actual times. If the index plan is faster, the planner's row estimates or cost constants such as `random_page_cost` are wrong, and that is what I fix. If the scan stays and shows `Disabled: true`, no index can serve the predicate. Leaving it off globally would push every query, including the ones where a sequential scan is right, onto worse plans.
code
sql · 8 linesBEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE)
SELECT member_id, sum(points)
FROM loyalty_ledger
WHERE earned_at >= now() - interval '7 days'
GROUP BY member_id;
ROLLBACK;go deeper
Recall that the enable_* settings discourage a plan type rather than forbid it, and that you run the test in one session or transaction, never server-wide.
Explain the experiment: EXPLAIN ANALYZE with and without the switch, and what a faster forced plan, a slower one, or a Disabled: true node each tells you.
Show that a faster forced plan sends you to the misestimate or the wrong cost constant behind it, and that you keep switches out of server-wide configuration.
Weigh steering a planner without hints: statistics, cost constants and query shape are the levers, plus a written policy for the rare statement that must be pinned.
## What the enable_* switches are PostgreSQL has **no optimizer hint syntax** in core. Instead, the planner exposes a family of boolean settings named `enable_*`. Each one switches a plan node type, or in a few cases a planner transformation, on or off. They are ordinary user-settable parameters, so any session can change them with `SET` and no reload or restart is involved. | setting | what turning it off discourages | default | |---|---|---| | `enable_seqscan` | Seq Scan, including the parallel form | on | | `enable_indexscan` | Index Scan and Index Only Scan | on | | `enable_bitmapscan` | Bitmap Index Scan + Bitmap Heap Scan | on | | `enable_nestloop` | Nested Loop joins | on | | `enable_hashjoin` / `enable_mergejoin` | Hash Join / Merge Join | on | | `enable_partitionwise_join` | joining partitions pair by pair | **off** | | `enable_self_join_elimination` (new in 18) | removal of redundant self-joins | on | The last two rows show that not every switch is a 'turn something off' tool: `enable_partitionwise_join` is opt-in, and `enable_self_join_elimination` controls a rewrite rather than a node type. ## What 'off' really means - **Discourage, not forbid.** The manual says it is impossible to suppress sequential scans entirely. A table with no usable index is still read with a Seq Scan, because there is no other way to read it. - **PostgreSQL 18 counts disabled nodes.** Every candidate path carries a count of disabled nodes, and the planner prefers the path with fewer of them; cost only breaks ties between paths with equal counts. `EXPLAIN` prints `Disabled: true` under a node the planner used despite the setting. - **Before 18**, the planner added a very large constant to the cost of a disabled path. Forced plans showed enormous total costs, which made the numbers hard to compare, and several penalties could add up in surprising ways. ## Reading the experiment 1. Run `EXPLAIN (ANALYZE)` on the slow query as it is and note the actual time. In 18, `ANALYZE` also reports buffer usage by default. 2. Open a transaction, run `SET LOCAL enable_seqscan = off`, run `EXPLAIN (ANALYZE)` again, then roll back. 3. Run each version at least twice, so the second plan does not win only because the first run warmed the cache. 4. Compare the outcomes: - **Forced plan faster**: the planner misjudged it. Compare estimated and actual rows (a statistics problem) or check whether cost constants such as `random_page_cost` still describe the storage. - **Forced plan slower**: the planner was right. The predicate matches enough of the table that reading it in order is cheaper. - **Still a Seq Scan with `Disabled: true`**: no index can answer this predicate. Typical causes are a function or cast around the indexed column, a parameter of a different type, or an operator the index's operator class does not support. ## Why it is not the fix - **It acts on every query in its scope.** A sequential scan is the right plan for small tables and for predicates that match a large fraction of rows; a global `off` pushes those queries onto index plans that read the same pages in random order. - **Server-wide changes are invisible regressions.** Put in `postgresql.conf`, it changes plans for every application and every future query, and nothing tells you which ones got worse. - **It hides the root cause.** If the estimate is wrong, the same estimate will mislead the planner on the next query that touches those columns. - **A forced shape outlives the data.** When the table grows or its distribution changes, the plan you forced may become the bad one. The durable fixes sit elsewhere: fresher or richer statistics, cost constants that match the storage, a rewritten predicate, or an index that matches it. If one statement truly must be steered, scope the switch to that transaction or to the one function that runs it, and write down why. ## The interview point Interviewers ask this to see whether a candidate treats the switch as a measuring instrument. The strong answer names the experiment, reads all three outcomes, and ends at a root cause instead of a configuration change.
- With enable_seqscan off, PostgreSQL 18 still shows a Seq Scan marked Disabled: true. What does that tell you?No other access path can answer the predicate, so the planner used the disabled node because it had nothing else. Look at the query and the index together: a function or cast around the column, a parameter of another type, or an operator the index's operator class does not support. The fix is in the query or the index definition, not in planner settings.
- How does enable_self_join_elimination, new in PostgreSQL 18, differ from enable_seqscan?It does not discourage a plan node; it switches off a rewrite. With it on, the default, the planner removes an inner join of a table to itself when the join clause proves both sides are the same row, for example equality on a unique key, and scans the table once. Turning it off is a quick way to check whether that rewrite changed a plan after an upgrade to 18.
- Why were forced plans harder to read in EXPLAIN before PostgreSQL 18?Older releases added a very large constant to the estimated cost of every disabled path, so a plan that had to keep a disabled node showed a huge total cost, and penalties from several disabled nodes added up. PostgreSQL 18 keeps a separate count of disabled nodes, compares that count first, and prints Disabled: true instead of inflating the cost numbers.
- enable_seqscan = off guarantees PostgreSQL will never run a sequential scan.
- Set enable_seqscan = off in postgresql.conf so new indexes always get used.
- If the planner can produce an index plan, the index plan is always faster.
- The enable_* settings need a server restart before they take effect.
- PostgreSQL core reads optimizer hint comments to force a particular index.
Assumes PostgreSQL 18: disabled-node counting and the EXPLAIN 'Disabled: true' line are new in 18; earlier releases added a large cost penalty instead.