Do less work
A slow query usually reads too much data. Optimization is mostly about touching fewer rows and fewer bytes, which the database does when it has the right indexes and a good plan.
Read the plan
The query planner explains how it will run a query. Look for warning signs.
- Full scans on a large table where an index was expected.
- Bad estimates where the rows predicted differ wildly from actual.
- Expensive joins that build huge intermediate sets.
Indexes that fit the query
- Match the filter so the index covers the where clause columns.
- Column order matters in composite indexes, lead with the equality columns.
- Covering indexes include the selected columns so the table need not be read.
Other wins
- Select fewer columns to move less data.
- Avoid functions on indexed columns that block index use.
- Keep statistics fresh so the planner estimates well.
Verify
Re run the plan after each change. An index helps only if the planner actually chooses it and the row counts drop.
Key idea
Read the plan, add indexes that match the filter, and verify the planner now touches far fewer rows.