Cost parameters for SSD + Mini-project: a planner that ignores an index

You close module 7 with two things. First, the cost parameters the planner uses to decide between plans. You already saw in capsule 02 that the defaults (seq_page_cost = 1.0, random_page_cost = 4.0) are calibrated for HDD hardware where random access is 4x more expensive than sequential. On SSD that difference is ~1.1x. Without adjusting, the planner overestimates the cost of Index Scan and prefers Seq Scan when it shouldn't. It's one of the highest-impact and lowest-risk tunings on any modern installation.

Second, the module's mini-project: a bookstore API with a planner that ignores an obvious index. You're going to apply everything from capsules 02-07 to diagnose the problem from scratch, identify the three root causes (old stats, missing extended stats, cost params for HDD), apply the fixes, and validate the improvement with before/after numbers.

By closing this capsule you're going to have internalized the module's complete cycle: detect → diagnose → fix → validate. And the mental pattern for reacting when someone tells you "this query was fast yesterday, today it takes 8 seconds."

Cost parameters: the planner's other input

Reminder of the formula you saw in capsule 02:

cost = pages × page_cost + rows × tuple_cost

The page_cost and tuple_cost are the cost parameters. PostgreSQL exposes them as configuration variables:

• seq_page_cost = 1.0 (default): cost of reading a page sequentially.
• random_page_cost = 4.0 (default): cost of reading a random page.
• cpu_tuple_cost = 0.01 (default): cost of processing a tuple on CPU.
• cpu_index_tuple_cost = 0.005 (default): cost of processing an index entry.
• cpu_operator_cost = 0.0025 (default): cost of evaluating an operator.
• effective_cache_size = 4GB (default): how much memory the planner estimates is available for the OS cache.

These numbers aren't seconds or milliseconds — they're arbitrary relative units. What matters is the ratio between them. The default ratio random / seq = 4.0 is what's calibrated for HDD. On SSD/NVMe that ratio should be ~1.1.

Why it matters

Concrete case. A table of 1M rows with 18,432 pages, and an index on customer_id. Query: SELECT * FROM orders WHERE customer_id = X that returns ~50 rows.

Cost of Seq Scan:

cost = 18432 × seq_page_cost (1.0) + 1000000 × cpu_tuple_cost (0.01)
     = 18,432 + 10,000 = 28,432

Cost of Index Scan with random_page_cost = 4.0 (HDD default):

cost ≈ 50 × 4.0 + some small costs
     ≈ 200

A 142x difference. The planner picks Index Scan with absolute confidence.

Cost of Index Scan with random_page_cost = 1.1 (SSD):

cost ≈ 50 × 1.1 + some small costs
     ≈ 55

Even more obvious. For this query, the change doesn't affect the decision.

But consider another query that returns 5,000 rows:

Cost of Seq Scan: ~28,432 (unchanged).

Cost of Index Scan with random_page_cost = 4.0:

cost ≈ 5000 × 4.0 = 20,000

A 1.4x difference. The planner picks Index Scan, but by a narrow margin.

Cost of Index Scan with random_page_cost = 1.1:

cost ≈ 5000 × 1.1 = 5,500

A 5x difference. The planner picks Index Scan with much more confidence.

On queries in the "intermediate" range (that return between 1% and 10% of the table), the cost parameter changes decisions. With the HDD default, the planner discards Index Scan on queries where it would be optimal. On SSD it's the right decision.

effective_cache_size and why it matters

effective_cache_size isn't a real PostgreSQL cache. It's a hint you give the planner about how much memory it estimates the OS has available to cache PostgreSQL pages. The planner uses it to estimate whether future reads of the same pages will be from RAM (fast) or from disk (slow).

Default: 4GB. Modern hardware: instances with 32GB-256GB of RAM are common. If you have 64GB and leave effective_cache_size = 4GB, the planner assumes only 4GB are available for cache — it underestimates the benefit of repeated Index Scans.

General recommendation: effective_cache_size = 75% × total_RAM. On a 32GB instance, effective_cache_size = 24GB.

seq_page_cost and random_page_cost for different hardware

In 2026, almost everything is SSD/NVMe. random_page_cost = 1.1 is the modern recommendation for most cloud installations.

How to apply

Three ways, in order of granularity:

1. Globally (in postgresql.conf):

random_page_cost = 1.1
effective_cache_size = 24GB

Reload required:

SELECT pg_reload_conf();

2. Per-database:

ALTER DATABASE production SET random_page_cost = 1.1;
ALTER DATABASE production SET effective_cache_size = '24GB';

3. Per-session (to experiment):

SET random_page_cost = 1.1;
EXPLAIN ANALYZE SELECT ...;
RESET random_page_cost;

For production, the global setting is the common one. Per-database is useful if there are different databases on the same instance with different hardware/usage.

Check the current settings

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'random_page_cost',
    'seq_page_cost',
    'effective_cache_size',
    'cpu_tuple_cost',
    'cpu_index_tuple_cost',
    'cpu_operator_cost'
)
ORDER BY name;

Typical output of an instance with defaults:

name                   | setting | unit  | context
cpu_index_tuple_cost   | 0.005   |       | user
cpu_operator_cost      | 0.0025  |       | user
cpu_tuple_cost         | 0.01    |       | user
effective_cache_size   | 524288  | 8kB   | user
random_page_cost       | 4       |       | user      <-- HDD DEFAULT
seq_page_cost          | 1       |       | user

effective_cache_size is reported in 8KB blocks. 524288 × 8KB = 4GB (the default).

Mini-project: a bookstore with a planner that ignores an index

You're going to build a reduced version of the bookstore with three simultaneous problems planted deliberately. Your job is to diagnose and fix.

Bookstore setup

DROP TABLE IF EXISTS books_project;
CREATE TABLE books_project (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    category TEXT NOT NULL,
    publisher TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    stock INTEGER NOT NULL,
    published_year INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes the bookstore uses
CREATE INDEX idx_books_author ON books_project(author);
CREATE INDEX idx_books_category ON books_project(category);
CREATE INDEX idx_books_publisher ON books_project(publisher);
CREATE INDEX idx_books_year ON books_project(published_year);

-- Initial load: 100k books distributed
INSERT INTO books_project (title, author, category, publisher, price, stock, published_year)
SELECT
    'Book ' || generate_series,
    (ARRAY['Author A', 'Author B', 'Author C', 'Author D', 'Author E'])[floor(random() * 5)::INT + 1],
    (ARRAY['Fiction', 'NonFiction', 'Tech', 'Children', 'Reference'])[floor(random() * 5)::INT + 1],
    -- Publisher correlated with category
    CASE
        WHEN random() < 0.5 THEN 'Penguin'
        WHEN random() < 0.7 THEN 'O''Reilly'
        ELSE 'Generic Press'
    END,
    (random() * 100)::NUMERIC(10, 2) + 10,
    (random() * 50)::INT,
    1990 + (random() * 35)::INT
FROM generate_series(1, 100000);

ANALYZE books_project;

Problem #1: bulk insert without ANALYZE

-- Load 500k additional books from 2026 (recent releases)
INSERT INTO books_project (title, author, category, publisher, price, stock, published_year)
SELECT
    'Book ' || (100000 + generate_series),
    (ARRAY['Author A', 'Author B', 'Author C', 'Author D', 'Author E'])[floor(random() * 5)::INT + 1],
    'Tech',  -- All of them Tech
    'O''Reilly',  -- All from O'Reilly (clear correlation)
    (random() * 100)::NUMERIC(10, 2) + 30,
    (random() * 100)::INT,
    2026
FROM generate_series(1, 500000);

-- IMPORTANT: we DON'T run ANALYZE — this is part of the planted problem

Diagnosis

The problem query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books_project
WHERE category = 'Tech' AND publisher = 'O''Reilly' AND published_year = 2026;

Your job (Step 1): run the query and observe the output. What's the difference between rows= (estimated) and actual rows=? Which plan did it pick? Is it optimal?

Apply fix #1: ANALYZE

ANALYZE books_project;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books_project
WHERE category = 'Tech' AND publisher = 'O''Reilly' AND published_year = 2026;

Your job (Step 2): did the estimate improve? Did the plan change? Is the query faster?

Problem #2: correlated columns without extended statistics

You notice that the estimate still isn't perfect. The planner assumes independence between category, publisher, and published_year, but the data is correlated (Tech 2026 implies O'Reilly because of how we loaded it).

Your job (Step 3): create extended statistics for the three correlated columns.

CREATE STATISTICS stat_books_category_publisher_year (dependencies, ndistinct, mcv)
ON category, publisher, published_year FROM books_project;

ANALYZE books_project;

Re-run and observe.

Problem #3: cost parameters for HDD

The stats are perfect but the chosen plan still gives you the feeling that it's not optimal. Check the cost parameters:

SHOW random_page_cost;
SHOW effective_cache_size;

If they're at the defaults (4.0 and 4GB), they're calibrated for HDD. You're (presumably) on SSD.

Your job (Step 4): adjust the cost parameters for SSD.

SET random_page_cost = 1.1;
SET effective_cache_size = '8GB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM books_project
WHERE category = 'Tech' AND publisher = 'O''Reilly' AND published_year = 2026;

Your job (Step 5): did the plan change? Did the total cost change? Compared with the initial state (step 1), what's the total improvement?

Improvement report

Your final objective is to produce a report like this:

Your report should document:

1. The three root causes identified.
2. The three fixes applied.
3. Before/after metrics with concrete numbers.
4. Conclusion: which fix had the greatest impact and why.

SELECT * FROM books_project WHERE author = 'Author B' AND price > 50;

Module close

You reached the end of module 7. What you learned across the 8 capsules:

• The planner's mental model: a deterministic box that takes statistics + cost parameters → the plan with the lowest cost.
• How the planner decides: pg_class, pg_stats, cost formulas for Seq Scan and Index Scan.
• Manual ANALYZE: when to run it (post bulk-load, post schema change, post massive DELETE) and how to detect old stats in EXPLAIN ANALYZE.
• Extended statistics: CREATE STATISTICS with dependencies/ndistinct/mcv for correlated columns.
• MVCC and bloat: why UPDATE/DELETE don't free space, how to detect bloat with pg_stat_user_tables and pgstattuple.
• Per-table autovacuum tuning: broken defaults for large tables, ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05).
• VACUUM FULL vs pg_repack: the first locks the table, the second doesn't. In production it's almost always pg_repack.
• Cost parameters for SSD: random_page_cost = 1.1, effective_cache_size = 75% RAM.

Before moving on to module 8 (anti-patterns + final integrative project), you should be able to:

• Look at an EXPLAIN ANALYZE and diagnose in under 30 seconds which of the module's 8 topics applies.
• Audit the autovacuum and cost parameters configuration on an existing database.
• Recommend the right tool (VACUUM, VACUUM FULL, pg_repack) for an operational scenario.
• Identify opportunities for CREATE STATISTICS in schemas with correlated columns.

We start in the next module

Module 8 closes the guide with common SQL/SQLAlchemy anti-patterns and a final integrative project that applies the techniques from ALL the modules. You're going to refactor a bookstore API with five performance problems planted on purpose (including two within this module's scope: delayed ANALYZE and accumulated bloat), measuring before/after with real benchmarks.

Before moving on, make sure to:

• Have pg_repack installed in your practice environment (module 8's capsule 03 is going to use it).
• Have pgstattuple active: CREATE EXTENSION pgstattuple;.
• Have your postgresql.conf with random_page_cost = 1.1 and effective_cache_size set to your hardware.

Resources

1. PostgreSQL Docs — Planner Cost Constants — official reference for all the cost parameters.
2. PostgreSQL Docs — effective_cache_size — detailed reference.
3. Crunchy Data — Postgres Hardware Configuration — a modern hardware tuning guide.
4. pgtune.leopard.in.ua — an online configuration calculator based on hardware.
5. Lukas Fittl — random_page_cost deep dive — deep analysis of the parameter.
6. Brandur Leach — Postgres tuning — an architectural view of tuning.
7. Citus Data — Postgres tuning checklist — an operational checklist.

Capsule 08 of 08 — Module 7 — Database Performance & Query Tuning Guide

End of module 7. Continue with module 8 to close the guide.