Planner Configuration and Query Rewrites

No extensions can be installed on this server — fix bad plans with the GUCs and query shapes PostgreSQL already ships with

Every GUC seen so far in this block — enable_hashjoin, enable_mergejoin — is a blunt instrument: a session-wide toggle meant for diagnosis, not a permanent fix. Flip one off, see what the planner does with the alternative forced on it, then flip it back with RESET. That same diagnostic technique applies to enable_seqscan, and PostgreSQL 18 makes the result easier to read than ever: a disabled strategy that was still the only option left now prints "Disabled: true" directly in the plan, instead of leaving you to infer it from an unusually high cost.\n\nTwo other levers matter just as much and are permanent, not diagnostic: work_mem, which decides whether a large sort or hash happens in memory or spills to disk, and how a query is written, which decides whether the planner can see through a Common Table Expression or is forced to treat it as an opaque, materialized block. None of this needs an extension — pg_hint_plan is not installed on this server and cannot be, since there is no network access to fetch it and no compiler to build it. Every technique in this lab ships in PostgreSQL by default.

enable_seqscan as a Diagnostic, Not a Fix

SET enable_seqscan = off;
EXPLAIN SELECT * FROM big_a WHERE val = 5000;

Seq Scan on big_a  (cost=0.00..847.00 rows=1 width=8)
  Disabled: true
  Filter: (val = 5000)

With no index on val, Seq Scan is the only access path that exists — disabling it does not remove it, it just marks it. PostgreSQL 18's "Disabled: true" line makes that unambiguous: this plan was forced to use a strategy the session explicitly turned off, because nothing else was available. That is a diagnostic signal pointing at a missing index, not evidence that enable_seqscan fixed anything.

Add the Missing Index

CREATE INDEX idx_biga_val ON big_a(val);
SET enable_seqscan = off;
EXPLAIN SELECT * FROM big_a WHERE val = 5000;

Index Scan using idx_biga_val on big_a  (cost=0.29..8.31 rows=1 width=8)
  Index Cond: (val = 5000)

Cost drops from 847.00 to 8.31. The real fix was never the GUC — it was the missing index the GUC helped surface.

work_mem: The Same Sort, Two Memory Budgets

| work_mem | Sort Method | Execution Time | |----------|-------------|-----------------| | 64kB | external merge, Disk: 1128kB | 569.232 ms | | 32MB | quicksort, Memory: 2196kB | 193.833 ms |

Same query, same 50,000 rows, same result — nearly three times slower when the sort has to spill to disk because the memory budget for it was too small.

The MATERIALIZED CTE Fence

-- default (and explicit NOT MATERIALIZED): planner inlines the CTE
EXPLAIN WITH sub AS (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;
--  Index Scan using big_a_pkey on big_a  (cost=0.29..8.31 rows=1 width=8)

-- MATERIALIZED: planner treats the CTE as an opaque, computed-once block
EXPLAIN WITH sub AS MATERIALIZED (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;
--  CTE Scan on sub  (cost=722.00..1847.00 rows=1 width=8)
--    Filter: (id = 5)
--    CTE sub
--      ->  Seq Scan on big_a  (cost=0.00..722.00 rows=50000 width=8)

Cost 8.31 versus 1847.00, for the exact same logical query. Since PostgreSQL 12, a CTE is inlined by default whenever it is referenced only once and has no side effects — MATERIALIZED opts back into the old, pre-12 opaque behavior, and is occasionally still useful (forcing a CTE to compute once rather than being duplicated into every reference point), but it is a real fence, not a free hint.

Disabled: true

A PostgreSQL 18 EXPLAIN annotation shown on a plan node whose strategy was turned off with an enable_* GUC but had to be used anyway because no other access path existed. It turns a previously implicit signal (an unusually high cost with no obvious explanation) into an explicit one, making it immediately clear that the plan is not really "the seq scan strategy losing to the planner" — it is the only strategy left standing.

MATERIALIZED / NOT MATERIALIZED (CTE)

Since PostgreSQL 12, a CTE referenced exactly once with no side effects is inlined into the surrounding query by default, letting outer WHERE clauses and joins push down into it exactly as if it had been written as a subquery. Writing MATERIALIZED forces the old pre-12 behavior instead: the CTE is computed once, in isolation, as an opaque block the rest of the query cannot see into or push conditions down through — a deliberate planning fence, occasionally still useful for forcing one-time computation of something expensive that is referenced more than once.

🚧 Disable Seq Scan With No Index to Fall Back On

Turn off enable_seqscan against a column with no index at all, and see what PostgreSQL 18 does differently from just silently ignoring the setting.

psql -U postgres -d beer_db -c "SET enable_seqscan = off;" -c "EXPLAIN SELECT * FROM big_a WHERE val = 5000;"

student@lab:~$ psql -U postgres -d beer_db -c "SET enable_seqscan = off;" -c "EXPLAIN SELECT * FROM big_a WHERE val = 5000;" SET SET QUERY PLAN ------------------------------------------------------- Seq Scan on big_a (cost=0.00..847.00 rows=1 width=8) Disabled: true Filter: (val = 5000) (3 rows)

🔨 Add the Missing Index and Compare

Create the index that was actually missing, then rerun the identical disabled-seqscan query to see the real fix.

psql -U postgres -d beer_db -c "CREATE INDEX idx_biga_val ON big_a(val);"

psql -U postgres -d beer_db -c "SET enable_seqscan = off;" -c "EXPLAIN SELECT * FROM big_a WHERE val = 5000;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_biga_val ON big_a(val);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "SET enable_seqscan = off;" -c "EXPLAIN SELECT * FROM big_a WHERE val = 5000;" SET SET QUERY PLAN -------------------------------------------------------------------------- Index Scan using idx_biga_val on big_a (cost=0.29..8.31 rows=1 width=8) Index Cond: (val = 5000) (2 rows)

💾 Force an External Sort With a Tiny work_mem

Shrink work_mem down to 64kB and sort all 50,000 rows of big_b by val, watching the sort spill to disk.

psql -U postgres -d beer_db -c "SET work_mem = '64kB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM big_b ORDER BY val;"

student@lab:~$ psql -U postgres -d beer_db -c "SET work_mem = '64kB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM big_b ORDER BY val;" SET SET QUERY PLAN --------------------------------------------------------------------------------------------------- Sort (cost=6188.41..6313.41 rows=50000 width=12) (actual rows=50000.00 loops=1) Sort Key: val Sort Method: external merge Disk: 1128kB Buffers: shared hit=249, temp read=278 written=308 -> Seq Scan on big_b (cost=0.00..746.00 rows=50000 width=12) (actual rows=50000.00 loops=1) Buffers: shared hit=246 Planning: Buffers: shared hit=61 read=3 Planning Time: 10.743 ms Execution Time: 569.232 ms (10 rows)

⚡ Raise work_mem and Watch the Sort Move to Memory

Set work_mem to 32MB and run the identical sort again to measure the real difference.

psql -U postgres -d beer_db -c "SET work_mem = '32MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM big_b ORDER BY val;"

student@lab:~$ psql -U postgres -d beer_db -c "SET work_mem = '32MB';" -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM big_b ORDER BY val;" SET SET QUERY PLAN --------------------------------------------------------------------------------------------------- Sort (cost=4648.41..4773.41 rows=50000 width=12) (actual rows=50000.00 loops=1) Sort Key: val Sort Method: quicksort Memory: 2196kB Buffers: shared hit=249 -> Seq Scan on big_b (cost=0.00..746.00 rows=50000 width=12) (actual rows=50000.00 loops=1) Buffers: shared hit=246 Planning: Buffers: shared hit=69 Planning Time: 6.253 ms Execution Time: 193.833 ms (10 rows)

🧱 Find the MATERIALIZED CTE Planning Fence

Compare a default (inlined) CTE against an explicitly MATERIALIZED one, filtering by primary key from outside the CTE in both cases.

psql -U postgres -d beer_db -c "EXPLAIN WITH sub AS (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;"

psql -U postgres -d beer_db -c "EXPLAIN WITH sub AS MATERIALIZED (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN WITH sub AS (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;" SET QUERY PLAN ------------------------------------------------------------------------ Index Scan using big_a_pkey on big_a (cost=0.29..8.31 rows=1 width=8) Index Cond: (id = 5) (2 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN WITH sub AS MATERIALIZED (SELECT * FROM big_a) SELECT * FROM sub WHERE id = 5;" SET QUERY PLAN --------------------------------------------------------------------- CTE Scan on sub (cost=722.00..1847.00 rows=1 width=8) Filter: (id = 5) CTE sub -> Seq Scan on big_a (cost=0.00..722.00 rows=50000 width=8) (4 rows)

Lab 3.1.4 complete. Planner configuration and query rewrites, using nothing beyond core PostgreSQL:\n\n\n enable_seqscan off, no index : ✅ "Disabled: true" — forced anyway, real fix was the index\n Index added, GUC re-tested : ✅ cost 847.00 → 8.31\n work_mem 64kB vs 32MB : ✅ external merge Disk vs quicksort Memory, 569ms → 194ms\n MATERIALIZED CTE fence : ✅ cost 8.31 → 1847.00, same logical query\n

Enable JavaScript to run the live terminal and track your progress.