LEARN. PRACTICE. GROW.Start with the contract and measurement
A fictional overnight report grew from seconds to minutes after task volume increased. Before creating an index, confirm the query, parameters, population, and required result. A version with LIMIT 10 does not measure the same work as a complete export. Collect the estimated plan and, within an authorized environment and scope, representative execution measurements. Distinguish server time from transfer and client processing. Also record whether the trial used a warm cache and what concurrent load existed. The objective is an observable hypothesis: reduce examined rows, correct an estimate, or avoid an expensive sort without changing semantics.
Cost, rows, and loops have their own units
cost=2..140 is an estimate in planner-model units, not a duration of 140 milliseconds. rows describes estimated node output, which may differ from examined rows. In a synthetic excerpt, an inner node emits exactly four rows in each of 250 executions: actual rows=4.00 and loops=250 correspond to 1000 emissions. Do not conclude that these are 1000 distinct identities. Higher-node costs include lower-node costs; summing the entire tree double-counts work. Also consider partial consumption: a LIMIT parent can stop a child before it produces its estimated complete population. Compare quantities describing the same scope.
Find the first relevant divergence
If a fully consumed node executed once estimates 20 rows but produces 2000, investigate underestimation before forcing a join method. Stale statistics, skewed distribution, and correlated conditions are different hypotheses. ANALYZE collects statistics; it guarantees neither perfect estimates nor equivalence to the EXPLAIN ANALYZE option. Extended statistics can help certain relationships between columns but do not automatically solve every query. Compare small and large parameter populations. A prepared query can use a generic or custom plan, and the useful choice can vary with values. Avoid turning one slow case into a global change before understanding its impact on other workloads.
Indexes follow access patterns
A B-tree on tenant_id,state,created_at is a candidate for equalities on the first two columns and a range on the third. That is a design hypothesis, not a guarantee of planner selection. In PostgreSQL 18, skip scan can make a composite index useful even without a first-column condition when distribution permits beneficial repeated searches. A partial index requires the query to imply its predicate; a generic plan using state=$1 cannot always assume state equals failed. A lower(email) lookup may justify indexing that expression while preserving semantics and collation. Every index adds storage and write maintenance, so evaluate the complete workload.
Coverage, visibility, and ordering
INCLUDE stores columns as payload to help cover a query; it does not turn them into ordered search keys. Even in an Index Only Scan, the engine can visit the heap to check visibility when the available information cannot avoid it. Heap Fetches does not automatically mean physical disk reads because pages may be cached. In another plan, combining indexes through a bitmap can improve row location but loses the original indexes’ logical ordering. The query still needs ORDER BY when order is part of its contract. A Seq Scan on a small table or broad selection can be appropriate. A node name alone does not determine execution quality.
Measure without equating rollback with no effects
EXPLAIN without ANALYZE shows the estimated plan without executing the analyzed statement. EXPLAIN ANALYZE executes it: a DELETE can remove rows and a called function can produce effects. Wrapping a trial in BEGIN and ROLLBACK can revert transactional changes but does not justify promising that nothing happened. In PostgreSQL, values obtained through nextval are not reclaimed by rollback. Identify functions, triggers, sequences, and integrations before choosing the trial environment. To diagnose only a write’s plan, begin without ANALYZE. JSON changes output format; it does not disable execution. This lesson’s data is synthetic and is not output measured on a PostgreSQL server.
Laboratory: result before and after an index
The code creates six synthetic tasks, selects tenant A failures, and repeats the same query after creating a composite index. Predict identifiers 2 and 4 in both reads. The local SQLite check confirms that this example preserves its result without proving a PostgreSQL performance gain. In a dedicated PostgreSQL laboratory, collect before-and-after plans using the same population and parameters; do not impose one node name as a universal test result. To practice reading without a server, use the synthetic excerpt of four rows across 250 loops and calculate the total. Record evidence of results, interpretation, and measured time separately.
Summary for support and capacity planning
During a latency incident, retain query, parameters, plan, population, metrics, and execution context as reproducible evidence. If Sort spills temporary data to disk, test the memory hypothesis with known session limits and concurrency. work_mem is not one budget for the entire server: multiple operations and sessions can multiply consumption, and other components have their own limits. One fast isolated execution does not validate a global change. Connect this lesson to observability, pagination, and transactions: reducing the wrong work does not improve correctness, and increasing resources does not solve every plan. The final decision should preserve results and demonstrate benefit under relevant conditions, including index maintenance cost.
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
tenant_id TEXT NOT NULL,
state TEXT NOT NULL,
created_at INTEGER NOT NULL
);
INSERT INTO tasks VALUES
(1,'A','done',10), (2,'A','failed',11),
(3,'B','failed',12), (4,'A','failed',13),
(5,'A','done',14), (6,'A','failed',20);
SELECT id FROM tasks
WHERE tenant_id='A' AND state='failed'
AND created_at>=10 AND created_at<15
ORDER BY created_at,id;
CREATE INDEX tasks_lookup ON tasks(tenant_id,state,created_at);
SELECT id FROM tasks
WHERE tenant_id='A' AND state='failed'
AND created_at>=10 AND created_at<15
ORDER BY created_at,idA node emits four rows in each of 250 loops: that is 1000 emissions. The calculation is synthetic and does not measure a server.
Common pitfalls
Do not treat cost as milliseconds, sum inclusive costs, ignore loops, promise heap-free scans, or use LIMIT to hide required work.
Related topics: Indexes and execution plans · Integrity, identity, and write conflicts · Pagination and result consistency
An index is a hypothesis: preserve semantics, interpret the plan, and measure benefit and cost on the real workload.
Reference: Reading estimates and execution plans · PostgreSQL 18 reference semantics; DR SQL 2026.4; synthetic plan metrics and portable SQLite 3.51.2 examples