Postgres tuning mistakes I'd check first, part 2: query plans and maintenance

┌─[fahim@BIDADARI:~]

└─$ less posts/2026-10-17-postgres-tuning-part-2-query-plans-and-maintenance.md

17 Oct 2026

This is part 2 of a series on Postgres tuning mistakes. Part 1 covers the setup, how I measured, and mistakes 1 to 5 (memory and connections). This part is about how the planner picks a plan, and the maintenance that keeps it picking good ones. Part 3 covers extensions.

Same setup as before: Postgres 18.6 in a 2-CPU, 2 GiB pod on a laptop k3d cluster, shared_buffers = 512MB, a 10M-row orders table, and a Go load generator running in the same cluster. Each section names the scenario that produced its numbers (make run S=stats and so on), and all of them are in postgres-tuning.


6. Autovacuum defaults on a large table

Autovacuum vacuums a table when its dead rows exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, which is 50 + 0.2 × rows by default (docs). For a 100M-row table that’s 20M dead rows before anything happens. PG 18 adds autovacuum_vacuum_max_threshold, which caps the result at 100,000,000 by default. With the default scale factor that cap only kicks in above 500M rows, so it changes nothing for a 10M-row table.

Check.

SELECT s.relname, s.n_live_tup, s.n_dead_tup, s.last_autovacuum,
       least(current_setting('autovacuum_vacuum_threshold')::float8
             + current_setting('autovacuum_vacuum_scale_factor')::float8 * c.reltuples,
             current_setting('autovacuum_vacuum_max_threshold')::float8) AS triggers_at
FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC LIMIT 10;

(This ignores per-table overrides in pg_class.reloptions; the scenario code handles those.) For actual bloat, pgstattuple gives exact numbers; there’s more on it in part 3.

Fix. Lower the scale factor on the big, busy tables only:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01);

Verify. n_dead_tup stays bounded and last_autovacuum moves.

What the demo showed (make run S=autovacuum). A 5M-row table, then 10% of its rows updated. With the defaults, autovacuum waits for 1,000,048 dead rows, so 500,000 isn’t enough:

State n_dead_tup last_autovacuum Heap fetches in the index-only scan Mean of 5
freshly vacuumed 0 never 0 92 ms
500k rows updated, 90 s later 500,000 never 550,010 328 ms
autovacuum_vacuum_scale_factor = 0.01 0 ran within 2 minutes 0 91 ms

Meter from 0 to 1.1M dead rows: 500,000 dead rows sit past the ~50k trigger for scale_factor 0.01 but well short of the default trigger at 1,000,048

(With the default scale factor, 500,000 dead rows is only halfway to the trigger.)

The query counts rows for a range of accounts with an index-only scan. Index-only scans skip the table only for pages the visibility map marks all-visible, and only VACUUM sets those bits. After the update every touched page needed a heap visit again, and the same query got 3.5 times slower. Nothing broke, and no error showed up anywhere. The query just stayed slow, waiting for enough dead rows to pile up.


7. random_page_cost = 4 when the data is cached

random_page_cost (default 4.0) tells the planner that a random page read costs four times a sequential one. The PG 11 docs suggested that SSDs “might also be better modeled with a lower value for random_page_cost, e.g., 1.1” (PG 11 docs). Current docs dropped the SSD sentence and say instead: “if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, … decreasing random_page_cost might be appropriate” (docs). Either way, at 4.0 the planner overprices index access and picks sequential scans and hash joins where an index would be faster.

Check. Find queries that sequential-scan a big table to read a small part of it, then compare plans in your session:

SHOW random_page_cost;
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) <the query>;
RESET random_page_cost;

Fix. Set it for the database (ALTER DATABASE app SET random_page_cost = 1.1) or server-wide once you’ve checked the important queries.

Verify. EXPLAIN ANALYZE of the affected queries before and after, plus the pg_stat_statements totals so you notice anything that got worse.

What the demo showed (make run S=randompagecost). A revenue-by-tier report for three cities (30k customers, 300k of the 10M orders):

random_page_cost Plan Median of 5 warm runs
4 Parallel Hash Join, sequential scan of all 10M orders 2.23 s
1.1 Nested Loop, index scan on orders(customer_id) 1.25 s

My first attempt at this demo failed, and the failure was useful. A VIP-customer report (1% of customers) flipped plans nicely with max_parallel_workers_per_gather = 0, but didn’t flip at all with parallel query on, because the parallel nested loop was already cheaper at 4.0. Test with the settings production actually uses.


8. Stale or too-coarse statistics

The planner estimates each WHERE condition separately and multiplies the selectivities, which assumes the columns are independent. City and warehouse aren’t: every city here is served by exactly one warehouse.

Check. Estimated versus actual rows in EXPLAIN ANALYZE. A factor of 10 or more on a node that feeds a join, sort or limit is worth fixing.

Fix. ANALYZE after bulk loads (the docs recommend it). Raise the per-column statistics target for skewed columns. For correlated columns, create extended statistics (docs):

CREATE STATISTICS st_orders_city_warehouse (mcv) ON city, warehouse FROM st_orders;
ANALYZE st_orders;

Verify. Estimates close to actuals, and a different plan where it matters.

What the demo showed (make run S=stats). The query is “newest 20 orders for this city and this warehouse” on a 3M-row table, the kind of thing a support tool runs from two dropdowns. Most pairs match nothing, because the warehouse doesn’t serve that city.

Pair Statistics Estimated rows Actual rows Plan p50 of 10
Jakarta / SG-WH1 per column 310 0 walk created_at index backwards, filter 583 ms
Jakarta / SG-WH1 + CREATE STATISTICS (mcv) 1 0 bitmap scan on city, sort 32 ms
Jakarta / ID-WH1 per column 310 29,936 parallel index walk 8.1 ms
Jakarta / ID-WH1 + CREATE STATISTICS (mcv) 33,002 29,936 index walk, no parallel workers 0.9 ms

Two flows for the same query: per-column statistics estimate 310 rows, pick a backward walk of the created_at index and take 583 ms; with MCV statistics the estimate is 1 row, a bitmap scan on city takes 32 ms

(Same query, same zero rows. The estimate decides which plan the planner picks.)

The empty case is the dangerous one. The planner expected 310 matches spread through the table, so it planned to walk the created_at index from the newest end and stop after 20. With zero matches it walked all 3M index entries. The MCV list on (city, warehouse) knows the pair doesn’t occur.

While exploring in psql I tried the two textbook examples first, and neither showed a difference on this data. A join where the corrected estimate switched a nested loop to a hash join came out slower with the hash join (256 ms vs 158 ms warm). A GROUP BY country, city, warehouse where the ndistinct estimate fell from 100,000 to 100 kept the same HashAggregate plan. Better estimates don’t always mean a faster query, so check the actual runtime and not only the estimate.


9. pgx prepared statements switch to a generic plan on skewed data

This one is specific to drivers that prepare statements, and pgx does by default. Postgres plans the first five executions of a prepared statement with the actual parameter values. After that it builds a generic plan and keeps using it if its estimated cost “is not so much higher than the average custom-plan cost” (PREPARE docs). With skewed data, the generic plan is built for an average value and is wrong for the rare one.

Symptom. A query is fast when you paste it into psql with a literal and slow when the application runs it.

Check. On the application’s own connection (the counters are per session):

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

PG 16+ can show the generic plan directly: EXPLAIN (GENERIC_PLAN) SELECT ... WHERE status = $1. And auto_explain logs the plan the slow execution actually used.

Fix. Any of these:

// Keep prepared statements, always plan with the real values.
cfg.ConnConfig.RuntimeParams["plan_cache_mode"] = "force_custom_plan"

// Or use unnamed statements, which Postgres always plans with the values.
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec

plan_cache_mode can also be set per role with ALTER ROLE. The cost is planning on every call, which for this query was a fraction of a millisecond.

Verify. Driver latency for the rare value matches the psql latency.

What the demo showed (make run S=genericplan). The admin page “latest 50 orders with status X”, called eight times for busy statuses, then 20 times each for delivered and refunded on one connection:

pgx setup generic / custom plans delivered p50 refunded p50
default (QueryExecModeCacheStatement) 3 / 5 0.20 ms 135 ms
+ plan_cache_mode = force_custom_plan 0 / 8 0.29 ms 2.2 ms
QueryExecModeExec (no named statement) 0.49 ms 1.9 ms

Executions 1 to 5 of the prepared statement use custom plans, then a generic plan takes over; the generic plan is 0.20 ms for delivered and 135 ms for refunded, while a custom plan for refunded is 2.2 ms

(The generic plan is fine for the common value and slow for the rare one. Orange is the generic plan, blue the custom plan.)

The generic plan walks the created_at index backwards and filters on status = $1. That’s right for delivered and terrible for refunded, which needs to scan about 500k index entries to find 50 matches. The custom plan for refunded uses the status index and sorts 973 rows.


10. Foreign key columns without an index

Postgres requires an index on the referenced side of a foreign key (the primary key) but not on the referencing column. The docs explain why it’s often worth adding anyway: a DELETE on the parent “will require a scan of the referencing table for rows matching the old value” (docs).

Check. Foreign keys whose columns aren’t the leading columns of any index:

SELECT c.conrelid::regclass AS table_name,
       string_agg(a.attname, ', ' ORDER BY x.n) AS columns,
       c.confrelid::regclass AS referenced_table,
       pg_size_pretty(pg_relation_size(c.conrelid)) AS table_size
FROM pg_constraint c
CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = x.attnum
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
  )
GROUP BY c.oid, c.conrelid, c.confrelid
ORDER BY pg_relation_size(c.conrelid) DESC;

On the demo database this found the table I built without the index, plus customers(city_id) -> cities. The second one is fine, since nobody deletes cities. Not every hit needs an index. The ones that matter are on large child tables whose parent rows get deleted or have their keys updated.

Fix. CREATE INDEX CONCURRENTLY ON child (parent_id);

Verify. EXPLAIN ANALYZE of a parent delete shows the time spent in the foreign key trigger.

What the demo showed (make run S=fkindex). Deleting 100 inactive customers (who have no orders) from a 100k-row parent with a 2M-row child table:

Time in Trigger for constraint fk_orders_customer_id_fkey Total
no index on fk_orders.customer_id 17,852 ms (100 calls) 17.9 s
with the index (built in 1.2 s) 12 ms 13 ms

Every deleted parent row scanned the 2M-row child table once to prove there were no references. During those 18 seconds the delete also held row locks on the parents, so anything inserting orders for those customers would have queued behind it.


11. Unused and duplicate indexes

Every index is updated on every insert, and on every update that isn’t HOT. Each one adds WAL, which also has to be shipped to replicas and backups.

Check. Indexes never scanned since the last stats reset, skipping unique ones because those enforce constraints:

SELECT s.relname, s.indexrelname, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid))
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

And indexes whose columns are the same as, or a leading prefix of, another index on the same table:

SELECT a.indexrelid::regclass AS redundant, b.indexrelid::regclass AS covered_by
FROM pg_index a JOIN pg_index b
  ON a.indrelid = b.indrelid AND a.indexrelid <> b.indexrelid
WHERE a.indexprs IS NULL AND b.indexprs IS NULL
  AND a.indpred IS NULL AND b.indpred IS NULL
  AND NOT a.indisunique
  AND (b.indkey::text LIKE a.indkey::text || ' %'
       OR (b.indkey::text = a.indkey::text AND a.indexrelid > b.indexrelid));

(indkey is an int2vector. Its text form is the column numbers separated by spaces, which turns the prefix test into a LIKE. My first version compared arrays and found nothing, because array equality also compares lower bounds and int2vector starts at 0.)

Fix. Drop them with DROP INDEX CONCURRENTLY. Before you do, check that the stats window covers monthly jobs, and check replicas too: idx_scan is per server, so an index used only by reports on a read replica shows 0 on the primary.

Verify. Insert/update latency and WAL volume (pg_current_wal_lsn() deltas, or wal_bytes in pg_stat_statements).

What the demo showed (make run S=unusedindex). Four sessions inserting 100-row batches for 30 seconds:

Indexes on the table WAL per row Rows inserted per second
primary key + 1 used index 280 bytes 29,407
primary key + 8 (the used one, plus 7 unused, duplicate or redundant) 870 bytes 19,023

WAL per row tripled, and it was steady across three runs (279–280 bytes vs 859–997). Throughput wasn’t steady at all. The baseline came in at 69k, 42k and 29k rows/s on three runs, while the 8-index version stayed between 19k and 22k. The direction never changed, but if you only measure throughput on a shared machine, one run can tell you almost anything. WAL volume is the better number to compare.

Both detection queries worked, with one catch. The app’s query used ui_events_account_created_idx2, an exact copy of ui_events_account_created_idx, so the original showed idx_scan = 0. With an exact duplicate, which copy gets the scans is arbitrary. Run the duplicate check before the unused check, and drop duplicates first.


12. Checkpoints too frequent

If WAL fills max_wal_size before checkpoint_timeout passes, Postgres starts a checkpoint early. Frequent checkpoints mean more full-page images in WAL right after each one, and more write I/O.

Check. The log says checkpoints are occurring too frequently (with log_checkpoints, which is on in this PG 18 setup). And the counters, which moved to their own view in PG 17:

-- PG 17+
SELECT num_timed, num_requested, num_done, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;
-- PG 16 and older: checkpoints_timed, checkpoints_req in pg_stat_bgwriter

Mostly num_requested and few num_timed means WAL volume, not the timer, is driving checkpoints.

Fix. Raise max_wal_size so checkpoints are mostly timed. Keep checkpoint_completion_target at its default of 0.9 so writes are spread out.

Verify. The ratio of requested to timed checkpoints over a busy day.

No demo here. Checkpoint behavior depends on write volume and storage, and a laptop run would mostly show the laptop’s SSD.


13. JIT on queries that don’t need it

JIT compiles expressions to machine code for queries whose estimated cost is above jit_above_cost (100,000), with more expensive optimization above 500,000. It’s been on by default since PG 12. The docs are direct about the trade-off: “For short queries the added overhead of performing JIT compilation will often be higher than the time it can save” (docs). A query whose estimated cost is inflated can pay compile time it never gets back.

Check. The JIT block in EXPLAIN ANALYZE, and across the workload with pg_stat_statements (the jit_* columns arrived in PG 15):

SELECT left(query, 60), calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(((jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time) / calls)::numeric, 1) AS jit_ms_per_call
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY jit_ms_per_call DESC LIMIT 10;

Fix. If JIT is a large share of runtime for OLTP queries, turn it off for the application’s role (ALTER ROLE app SET jit = off) or raise jit_above_cost.

What the demo showed (make run S=jit). I couldn’t make JIT hurt on this data, and I’d rather report that than build a contrived case. Every query that crossed jit_above_cost ran for at least half a second. JIT then cost 20 to 110 ms of CPU, split across the leader and each parallel worker (each one compiles its own copy):

VIP report (cost 154,996) Median of 7
jit = on 584 ms
jit = off 555 ms

That 5% is within the run-to-run noise I saw on this machine. The check above is cheap, so run it before deciding either way.


14. Ignoring asynchronous I/O in PG 18

PG 18 can issue reads asynchronously, which the release notes say “can improve performance of sequential scans, bitmap heap scans, vacuums, and other operations” (PG 18 release notes). io_method picks the mechanism: worker (the default, using io_workers background processes, default 3), io_uring on Linux builds that support it, or sync for the old behavior. Changing it needs a restart (docs).

Check.

SHOW io_method;
SHOW io_workers;
SELECT * FROM pg_aios;  -- I/Os in flight right now

Fix. Start with the default. Try io_uring on Linux, and more io_workers on storage with high latency.

Verify. Sequential scan and vacuum throughput on data that isn’t cached, before and after.

What the demo showed (make aio). A single backend (no parallel workers) running count(*) on a 2.9 GB table, larger than the pod’s 2 GiB memory limit, with the OS page cache dropped before each restart:

io_method Scan 1 (cold) Scans 2 and 3 Time waiting on reads (scan 3) Peak I/Os in flight
sync 7.7 s, 398 MB/s 568, 564 MB/s 4.6 s 1
worker (default) 7.5 s, 411 MB/s 493, 493 MB/s 3.8 s 7
io_uring 7.5 s, 408 MB/s 543, 555 MB/s 3.5 s 7

No meaningful difference on this machine. All three cold scans took 7.5 to 7.7 seconds. worker and io_uring did keep 7 reads in flight where sync had 1, and the backend spent less time waiting on reads, but the scans didn’t get faster. I see two likely reasons. The storage is a local NVMe drive with kernel readahead, so a synchronous read rarely waits long. And the I/O workers compete with the scanning backend for the same 2 CPUs. Async I/O exists to hide I/O latency, and this setup doesn’t have much latency to hide. Network-attached volumes such as EBS or Persistent Disk add latency to every read, so measure on the storage you actually run before you decide.

io_uring did work inside the pod. Kubernetes runs containers without a seccomp profile unless the pod or the kubelet (seccompDefault) asks for one, while Docker’s default profile has blocked the io_uring syscalls since Docker 25 (moby #46762). Before you set io_method = io_uring in a container, check which profile applies and test a restart.

My first run of this scenario had sync at 38 MB/s on its first scan. It ran right after the 2.9 GB table was created, while the kernel was still writing those pages back to disk. Running all three methods after the table had settled gave the numbers above.


Summary of part 2

Same 2-CPU, 2 GiB Postgres 18 pod as part 1. “Before” is the mistake, “after” is the fix from that section.

Section What was measured Before After
6. Autovacuum index-only scan after a 10% update 328 ms, 550k heap fetches 91 ms, 0
7. random_page_cost regional report 2.23 s 1.25 s
8. Statistics “newest orders” for an empty city/warehouse pair 583 ms 32 ms
9. Generic plans rare-status query through pgx 135 ms 2.2 ms
10. FK index delete 100 parent rows 17.9 s 13 ms
11. Extra indexes WAL per inserted row 870 bytes 280 bytes
13. JIT VIP report 584 ms (on) 555 ms (off), within noise
14. Async I/O cold 2.9 GB scan, sync vs io_uring 7.7 s 7.5 s, within noise

Across both parts, two of the fourteen mistakes didn’t reproduce on this setup (JIT and async I/O), and two had no demo at all (shared_buffers in part 1, checkpoints here). Those sections still have the check queries. Run them against your own database, because your hardware and workload decide whether the mistake costs you anything.

Part 3 covers the extensions used along the way, driven from Go.

Sources

PostgreSQL documentation: autovacuum, random_page_cost (and the PG 11 text), CREATE STATISTICS, populating a database, PREPARE, foreign keys, JIT, io_method, PG 18 release notes.

Platforms: moby #46762 (Docker seccomp and io_uring).

Further reading, not cited above: pganalyze on Postgres 18 async I/O, pganalyze 5mins on planner tuning and extended statistics, postgresqlco.nf on random_page_cost, Better Stack’s PostgreSQL 18 async I/O guide.

← back to work