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:
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
| Setting | Default | The method it penalises |
|---|---|---|
enable_seqscan | on | Reading every page of a table. |
enable_indexscan | on | Finding rows through an index: a probe, a range, a key lookup, a vector index. |
enable_hashjoin | on | A hash join. |
enable_mergejoin | on | A merge join. |
enable_nestloop | on | A 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.
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:
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:
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:
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:
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.
Related
- Query Performance for
EXPLAINand statistics. - Memory for
work_mem, the per-operator memory a session sets.