Section 3 of 3 · 6 minutes
Change one cause and measure again
Your goal
What you will be able to do
Test one focused query, index, or statistics change against the original plan and result.
Why this matters at work
The practical reason
Several simultaneous changes make it hard to know what helped and can hide a regression.
Learn
The idea in plain English
State a testable cause: for example, stale statistics are underestimating rows, or an access path reads too many pages. Make the smallest safe change that tests that cause.
Run the same query with the same representative values. Compare result correctness, rows, loops, buffers, and elapsed time. Record any new write, storage, or maintenance cost.
Remember these points
- Write down one cause before changing anything.
- Keep query parameters and data conditions comparable.
- Verify correctness as well as speed.
See it in SQL
Check the statistics observation time
See whether table statistics may be stale before changing indexes or SQL.
SELECT
relname AS table_name,
n_live_tup AS estimated_live_rows,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'training_orders';The row estimate and analyze timestamps help test a stale-statistics hypothesis.
They do not prove statistics are wrong. Compare plan estimates with actual rows before deciding to analyze.
The estimated live rows and most recent manual or automatic analyze times for training_orders.
Useful words
Important terms
- Planner statistics
- Summaries PostgreSQL uses to estimate how many rows operations will return.
Quick check · Not graded
Check your understanding
Why should a performance test change one main variable at a time?
Your progress is saved to your signed-in account.