What it is
The Index & Query Optimization Checklist is the methodical pass a DBA runs to turn a slow query into a fast one — and to do it from evidence rather than guesswork. It is structured as a four-step workflow: find the real offenders, read the execution plan, fix the access path, and verify and clean up. Worked top to bottom, it stops you from guessing at indexes and points you at the specific node the planner is actually choking on. The promise is precision: you fix the row the engine is struggling with, not the one you assume is the problem.
Step one is about looking at the right queries. It pulls the top queries by total time — not just per-call time — from pg_stat_statements, Performance Schema, or Query Store, because a fast query called a million times can outweigh one slow report. It captures rows examined versus rows returned, since a high ratio is the classic fingerprint of a missing or unused index. Step two reads EXPLAIN (ANALYZE, BUFFERS) bottom-up to find the most expensive node — a sequential scan on a large table, a nested loop with a high loop count, or a sort spilling to disk — and checks estimated versus actual rows to spot stale statistics misleading the planner.
Step three fixes the access path with real technique: a composite index in the right column order (equality columns first, then range, then ORDER BY), a covering index that includes the selected columns so the engine skips the heap lookup, a partial index when queries always filter on the same predicate, and query rewrites that preserve SARGability. Step four verifies the win — re-running EXPLAIN ANALYZE to confirm the plan switched to an index scan, running ANALYZE so statistics reflect reality, and dropping redundant indexes that slow every write. A plan-red-flags table and a 'before you ship the index' Q&A round it out.
What it's used for
DBAs and backend engineers use this checklist to make database performance work disciplined and repeatable instead of a hunch-driven guessing game. The concrete jobs it does:
- ✓ Finding the queries that actually matter — ranking by total execution time and mean time from pg_stat_statements, Performance Schema, or Query Store, and confirming a query is genuinely hot in production rather than a one-off.
- ✓ Reading execution plans correctly — running EXPLAIN (ANALYZE, BUFFERS), reading bottom-up, and locating the most expensive node, whether a Seq Scan, a high-loop Nested Loop, or a Sort spilling to disk.
- ✓ Diagnosing planner misbehavior — comparing estimated versus actual rows to catch stale statistics, and checking whether the index is used for the filter, the join, and the ORDER BY or only some of them.
- ✓ Designing the right index — composite column order (equality, then range, then sort), covering indexes that include selected columns, and partial indexes for queries with a constant predicate.
- ✓ Rewriting queries for index use — replacing OR with UNION ALL, killing leading-wildcard LIKE, and avoiding functions on the indexed column that break SARGability.
- ✓ Verifying the improvement — re-running EXPLAIN ANALYZE to confirm the plan switched to an Index Scan or Index Only Scan and the time dropped, then running ANALYZE so estimates reflect reality.
- ✓ Cleaning up — dropping redundant and unused indexes (idx_scan = 0) that slow every write and inflate storage, and confirming the new index did not evict a hotter one from the buffer cache.
Who uses it
Query optimization is where database performance is won or lost, so the checklist is written for the people who diagnose slow systems and decide what to index.
Context & good to know
The deepest pitfall in database performance work is optimizing by intuition — adding an index because a query 'feels' slow, without reading the plan. This checklist exists to replace that with evidence: read what the planner is doing, find the most expensive node, and fix that. Reading EXPLAIN (ANALYZE, BUFFERS) bottom-up and comparing estimated to actual rows is the single most valuable skill it teaches, because the gap between the two is usually the real story — stale statistics steering the planner into a bad plan.
Indexing is a trade-off, never a free win, and the checklist is honest about that. Every index is maintained on every INSERT, UPDATE, and DELETE, so on a write-heavy table a rarely-used index is a net loss. Column order matters: a composite index on (a, b) helps WHERE a and WHERE a AND b but not WHERE b alone. And redundancy is silent waste — adding (a, b) when (a, b, c) already exists is pure overhead because the planner can use the prefix. The 'before you ship' Q&A is built to catch exactly these mistakes.
SARGability — whether a predicate can use an index — is where query rewrites earn their keep. Wrapping an indexed column in a function, leading a LIKE with a wildcard, or forcing the planner to evaluate an OR across columns all block index use no matter how good the index is. The checklist pairs index design with query rewriting because the fastest index in the world cannot help a query written in a way the planner cannot exploit. This is consistent across PostgreSQL, MySQL, and Oracle Database, even where the tooling differs.
Spotsaas places this checklist in its database resources because query performance is one of the most tangible differences teams feel between database engines and how they are run. Whether a team is on PostgreSQL, MySQL, MariaDB, or Oracle Database, the workflow — find the offender, read the plan, fix the access path, verify — is the same, and having it written down turns ad-hoc firefighting into a repeatable engineering practice.