The full answer
Find the queries that actually cost the most, read their execution plans to see where the time goes, then fix the cause, usually a missing or unusable index, too much data read, a bad join or stale statistics, and measure again.
1. Find the right query Look at the total time spent per query, not just the slowest single run: a 50 ms query run a million times a day matters more than one slow report.
- PostgreSQL:
pg_stat_statements. - SQL Server and Azure SQL: Query Store, and Extended Events (SQL Server Profiler is deprecated).
- MySQL: the slow query log and Performance Schema.
2. Read the plan
EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE (PostgreSQL, MySQL) or the actual execution plan (SQL Server) runs the query and shows real row counts and times per step. Look for:
- full scans of large tables where few rows are needed;
- estimated row counts far from the actual ones (stale statistics, or a correlation the optimizer can't see);
- sorts and hash operations spilling to disk;
- nested loops over large inputs.
3. Fix indexing
- Index columns used in selective
WHEREfilters and join keys; for several conditions, a composite index with equality columns first. - A covering index (with
INCLUDEcolumns in PostgreSQL and SQL Server) lets the query be answered from the index alone. - Make predicates usable by an index:
WHERE order_date >= '2026-01-01'can use an index onorder_date, butWHERE YEAR(order_date) = 2026usually can't.
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
4. Read less data
- Select only the needed columns instead of
SELECT *. - Filter as early as possible, and paginate with keyset pagination rather than large
OFFSETs.
5. Rewrite where it helps
- Replace row-by-row logic (cursors, loops) with set-based SQL.
- Check that
EXISTS,INand joins express what you mean;NOT INwith NULLs returns nothing. - Pre-aggregate in a CTE or derived table before joining when the join would multiply rows.
6. Keep statistics current
Run ANALYZE (PostgreSQL), UPDATE STATISTICS (SQL Server) or ANALYZE TABLE (MySQL) after large data changes.
7. Structural changes for very large tables
- Partitioning by date lets the planner skip whole partitions. In PostgreSQL:
CREATE TABLE orders (
order_id BIGINT NOT NULL,
order_date DATE NOT NULL,
total NUMERIC(12,2),
PRIMARY KEY (order_id, order_date) -- must include the partition key
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2026_10 PARTITION OF orders
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
- Materialized views or summary tables for repeated heavy aggregations; caching for results that change rarely.
8. Measure the result Compare the plan and the timing before and after with realistic data volumes; a change that helps on a small test table can hurt at production size.
Last updated 16 October 2026
Sources