Skip to main content

Planner Switches

By the end of this page you will know what enable_seqscan, enable_indexscan, enable_hashjoin, enable_mergejoin and enable_nestloop do, how to use them to see what another plan would cost you, and what they never do.

The five switches are PostgreSQL's, with the same names, the same on and off spellings and the same meaning: a method you switch off is not forbidden, it is made very expensive. The planner takes another method whenever one can run the statement. When none can, it plans the method you switched off, so a statement that runs with the switch on still runs with it off, and returns the same rows.

Examples on this page use these tables:

sql
CREATE TABLE sc (id INTEGER PRIMARY KEY, grp INTEGER, half INTEGER, pad TEXT);
INSERT INTO sc SELECT i, i % 400, i % 2, 'p' || i FROM generate_series(0, 3999) AS g(i);
CREATE INDEX sc_grp ON sc (grp);
CREATE INDEX sc_half ON sc (half);
ANALYZE sc;

CREATE TABLE big (k INTEGER NOT NULL, v TEXT);
CREATE TABLE small (k INTEGER NOT NULL, w BIGINT);
INSERT INTO big SELECT i % 1000, 'v' || i FROM generate_series(0, 4999) AS g(i);
INSERT INTO small SELECT i % 500, i FROM generate_series(0, 2499) AS g(i);
ANALYZE big;
ANALYZE small;

The switches​

SettingDefaultThe method it penalises
enable_seqscanonReading every page of a table.
enable_indexscanonFinding rows through an index: a probe, a range, a key lookup, a vector index.
enable_hashjoinonA hash join.
enable_mergejoinonA merge join.
enable_nestlooponA nested loop join.

Each is a session setting. It lasts for the session, or for the transaction with SET LOCAL, RESET returns to on, and RESET ALL and DISCARD ALL restore every switch. A value that is not a boolean is refused with 22023.

sql
SET enable_seqscan = off;
SHOW enable_seqscan;
RESET enable_seqscan;
SELECT set_config('enable_nestloop', 'off', false);
RESET enable_nestloop;

Scans​

With every switch on, a selective probe takes its index and an unselective one reads the table:

sql
EXPLAIN SELECT id FROM sc WHERE grp = 7;
EXPLAIN SELECT id FROM sc WHERE half = 0;

enable_seqscan = off makes the index win even where it saves nothing, and enable_indexscan = off makes the table scan win however selective the probe is:

sql
SET enable_seqscan = off;
EXPLAIN SELECT id FROM sc WHERE half = 0;
RESET enable_seqscan;

SET enable_indexscan = off;
EXPLAIN SELECT id FROM sc WHERE grp = 7;
RESET enable_indexscan;

With both off the two penalties cancel and the costs decide as they do with both on. A read, update or delete by primary key goes through the planner under enable_indexscan = off, so EXPLAIN shows the scan that runs, and it returns and changes the same rows as the indexed path.

Joins​

With every switch on, the planner takes a merge join where its inputs qualify and a hash join elsewhere. Switching the preferred method off moves the join to the next one:

sql
EXPLAIN SELECT b.k, s.w FROM big b JOIN small s ON b.k = s.k;
SET enable_mergejoin = off;
EXPLAIN SELECT b.k, s.w FROM big b JOIN small s ON b.k = s.k;
SET enable_hashjoin = off;
EXPLAIN SELECT b.k, s.w FROM big b JOIN small s ON b.k = s.k;
RESET enable_mergejoin;
RESET enable_hashjoin;

A join with no other method, such as a join on an inequality, still plans and runs with the switch off:

sql
SET enable_nestloop = off;
SELECT count(*) FROM big b JOIN small s ON b.k < s.k AND s.k < 3;
RESET enable_nestloop;

What a switch never does​

  • It never changes an answer. Every combination of the five switches returns the same rows for the same statement.
  • It never forbids a method. A disabled method adds a large fixed cost (PostgreSQL's disable_cost) to the plans that use it. PostgreSQL 18 counts disabled methods before it compares costs; ScramDB adds the cost, which separates the plans just as completely unless a plan costs more than the penalty itself, and no plan does.
  • It never shares a cached plan. Two sessions with different switches never reuse each other's cached plan, and a session that changes a switch plans its next statement afresh. A session that never touches a switch plans exactly as before.
  • It does not depend on the tier. The interpreter and the compiled tier read the same plan.