Performance tuning stops being guesswork the moment you understand what the database is actually doing with your query. This guide walks the whole pipeline - from how the optimizer turns SQL into a plan, to reading that plan, indexing for it, and rewriting queries the engine can run fast. It is the conceptual companion to our task-oriented SQL performance tuning checklist: read this to understand why, then run the checklist to fix things fast.
What this guide covers
- How the query optimizer works
- Reading execution plans
- Indexing deep dive
- Writing sargable queries
- Join performance
- Reducing the work the engine does
- Statistics and cardinality estimation
- Aggregation and sorting cost
- Caching, materialized views, and summary tables
- Partitioning and archiving large tables
- Monitoring: finding slow queries
- Biggest wins and symptom → cause → fix
How the query optimizer works
SQL is declarative: you describe the result you want, not the steps to produce it. Between your statement and the answer sits the query optimizer, the component that decides how to get the rows. Every mainstream engine - PostgreSQL, MySQL/InnoDB, SQL Server, Oracle - runs the same three-stage pipeline.
Parse
The text is tokenized and checked for syntax, then bound: table and column names are resolved against the catalog, types are validated, and views are expanded. The output is a logical query tree.
Plan (optimize)
The optimizer enumerates alternative ways to execute that tree - different join orders, join algorithms, index choices, scan methods - estimates the cost of each using table statistics, and keeps the cheapest.
Execute
The chosen physical plan is handed to the executor, which pulls rows through the operator tree and returns the result set.
The interesting stage is planning. A query joining four tables can be executed in dozens of logically equivalent ways that all return the same rows but differ in runtime by orders of magnitude. Modern engines are cost-based: rather than following fixed rules, they assign each candidate plan a numeric cost that models how much CPU and I/O it will take, then pick the minimum.
Where cost comes from
Cost is estimated, not measured. The optimizer leans on statistics collected in advance - row counts, the number of distinct values in a column, histograms describing data distribution, and the average width of a row. From these it estimates cardinality: how many rows each operator will emit. Cardinality drives everything downstream, because the cost of a join or sort depends on how many rows flow into it. A plan is only as good as its row estimates, which is why stale statistics (covered in section 7) are such a common cause of bad plans.
Key idea: the optimizer never guarantees the fastest plan - only the cheapest plan it could find given its estimates and time budget. When it is wrong, it is almost always because its cardinality estimate was wrong. Fix the input (statistics, sargable predicates, indexes) and the plan usually fixes itself.
The search space is huge, so optimizers do not evaluate every plan. They prune aggressively and give up early on large joins - PostgreSQL switches to a genetic algorithm past a join threshold, and SQL Server times out optimization and ships a "good enough" plan. This is why hand-written query structure still matters: you are narrowing the search space for a component that cannot explore all of it. To go deeper on how the planner reasons and how to influence it, see advanced query optimization.
Plan caching and reuse
Optimizing is itself expensive, so engines cache finished plans keyed by the statement text or a parameterized version of it. Reuse is great for throughput but has a dark side: a plan chosen for one parameter value can be terrible for another (parameter sniffing). A plan built for status = 'archived', which matches 5 million rows, may be reused for status = 'pending', which matches 12 - and lose. Recognizing "fast sometimes, slow other times for the same query" as a caching artifact is half the battle.
Reading execution plans
The execution plan is the ground truth of tuning. Every engine exposes it, and reading one fluently is the single highest-leverage skill in this whole guide. Get the plan, find the expensive operator, fix that, re-measure.
EXPLAIN vs EXPLAIN ANALYZE
EXPLAIN shows the plan the optimizer intends to run, with estimates only - it does not execute the query. EXPLAIN ANALYZE actually runs it and reports real timings and real row counts beside the estimates, which is what you want when the numbers look suspicious.
-- PostgreSQL: plan + real timings + buffer (I/O) counts
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4217
AND o.created_at >= '2026-01-01';-- MySQL 8+: readable tree first, then real execution
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_id = 4217;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4217;
-- SQL Server: SET STATISTICS then the actual plan
SET STATISTICS IO, TIME ON;
-- or click "Include Actual Execution Plan" in SSMSHow to read the tree
A plan is a tree that executes from the inside out: the most deeply nested node runs first and feeds its parent, so the leaves are where cost is born and the root is where it is paid. Read the leaves, find the fat node, start there. Here is a trimmed PostgreSQL plan and how to interpret it.
-- estimated (cost/rows) vs actual (time/rows/loops)
Nested Loop (cost=0.86..842.3 rows=1 width=16)
(actual time=0.05..612.4 rows=48211 loops=1)
-> Seq Scan on orders o
(cost=0.00..40.1 rows=1 width=8)
(actual time=0.02..210.9 rows=48211 loops=1)
Filter: (customer_id = 4217)
Rows Removed by Filter: 1951789
-> Index Scan using pk_customer on customers c
(actual time=0.01..0.01 rows=1 loops=48211)Three things jump out. The optimizer estimated rows=1 but the operator actually produced 48,211 - a catastrophic estimate that led it to pick a nested loop it would never have chosen for 48k rows. The Seq Scan read the whole table and threw away nearly two million rows (Rows Removed by Filter), a screaming signal for a missing index on customer_id. And the inner index scan ran loops=48211 times, so its tiny per-loop cost multiplies into the bulk of the runtime.
Scan types and what they mean
Most plan-reading comes down to recognizing a handful of access methods and knowing which one you want.
| Operator | What it does | Good or bad? |
|---|---|---|
| Seq scan / Table scan / Full scan | Reads every row in the table | Fine for small tables or when you truly need most rows; a red flag on a large, selectively filtered table |
| Index scan / Index seek | Navigates the index to the matching range, then fetches rows | What you usually want for selective filters |
| Index-only scan / Covering seek | Answers entirely from the index, never touching the table | Best case - no table lookups at all |
| Bitmap heap scan (PostgreSQL) | Collects matching row locations, then reads the table in physical order | Good middle ground for medium-selectivity filters |
| Key lookup / Bookmark lookup (SQL Server) | Extra hop from a nonclustered index back to the clustered index for missing columns | Cheap per row, costly in bulk - a hint to widen or cover the index |
Note the vocabulary difference: PostgreSQL and MySQL say index scan; SQL Server and Oracle distinguish an index seek (jump to a range) from an index scan (walk the whole index). A "seek" is the targeted, fast operation; an index scan in SQL Server can be nearly as expensive as a table scan.
Watch out: the biggest tell in any plan is a large gap between estimated and actual rows. When they diverge by 10× or more, the optimizer is planning for the wrong data shape and every downstream decision is suspect. Chase that gap before you touch anything else - it usually points at stale statistics or a non-sargable predicate.
Indexing deep dive
Indexes are the highest-return tuning lever, and misunderstanding them is the most common cause of slow SQL. An index is a separate, sorted data structure that lets the engine find rows without scanning the whole table - the database equivalent of a book's index versus flipping every page. For a gentler introduction, see SQL indexes explained; this section goes deep.
B-tree basics
The default index in every relational engine is the B-tree (technically a B+tree). It is a balanced, sorted tree: a root and internal nodes hold separator keys that route a search, and the leaf level holds every key in sorted order. Because it stays balanced, any lookup takes the same small number of steps - a few page reads even in a table of hundreds of millions of rows. That sorted structure is why one B-tree serves equality lookups (= 4217), range scans (BETWEEN, >, <), prefix matches (LIKE 'abc%'), and ORDER BY on the same columns.
Index types at a glance
| Index type | Structure | Best for |
|---|---|---|
| B-tree (default) | Balanced sorted tree | Equality, ranges, sorting, prefix LIKE - the workhorse |
| Hash | Hash table | Pure equality only; no ranges or ordering |
| Clustered | Table rows stored in index order | Range scans and the primary key; one per table |
| Nonclustered / secondary | Separate tree pointing back to rows | Additional access paths; many per table |
| Covering (INCLUDE) | B-tree carrying extra payload columns | Answering a query from the index alone |
| Composite / multi-column | B-tree keyed on several columns | Filters and sorts spanning multiple columns |
| Partial / filtered | Index over a subset of rows | Skewed data, e.g. only status='active' |
| GIN / inverted / full-text | Term → rows mapping | JSON, arrays, and full-text search |
Clustered vs nonclustered
A clustered index defines the physical order of the table - the table is the index, with full rows living in its leaf pages. There can be only one, because rows can only be stored in one order. In SQL Server the primary key is clustered by default; in MySQL/InnoDB the primary key is always the clustering key. A nonclustered (secondary) index is a separate tree whose leaves hold the indexed columns plus a pointer back to the row - the clustering key in InnoDB, a row locator in SQL Server. That pointer is why a secondary index that lacks a needed column triggers a second lookup per row.
PostgreSQL is the exception: its heap tables are unordered and all indexes are secondary, though CLUSTER can physically reorder a table once against an index (it is not maintained afterward).
Composite index column order
Column order in a multi-column index is the detail people get wrong most often. A B-tree on (a, b, c) is sorted by a first, then b within each a, then c. It can serve any leading prefix - (a), (a, b), (a, b, c) - but not (b) alone or (b, c), because those columns are not sorted independently of a.
The rule of thumb: put equality columns first, then the range or sort column last. A query filtering customer_id = 4217 AND created_at >= '2026-01-01' wants the index (customer_id, created_at) - the equality pins a single slice of the tree, and the range then reads a contiguous run within it.
-- equality column first, range column last
CREATE INDEX ix_orders_cust_date
ON orders (customer_id, created_at);
-- serves both filters AND the ORDER BY with no sort
SELECT id, total FROM orders
WHERE customer_id = 4217
AND created_at >= '2026-01-01'
ORDER BY created_at;-- range column first: the equality can't pin a slice,
-- so the index scans a wide range and re-filters
CREATE INDEX ix_bad
ON orders (created_at, customer_id);Covering indexes and selectivity
A covering index includes every column a query needs, so the engine answers it from the index alone - an index-only scan with zero table lookups. In SQL Server and PostgreSQL you attach non-key payload columns with INCLUDE so they ride along in the leaf without bloating the searchable key.
-- key on the filter, INCLUDE the columns the query returns
CREATE INDEX ix_orders_cover
ON orders (customer_id)
INCLUDE (total, status, created_at);
-- now this never touches the table (index-only scan)
SELECT total, status, created_at
FROM orders WHERE customer_id = 4217;Selectivity decides whether an index is worth using at all. A highly selective column has many distinct values, so a lookup returns few rows - email, user ID, order number. A low-selectivity column (a boolean, a gender flag, a status with three values) returns a huge fraction of the table, and the optimizer will often correctly ignore an index on it because a sequential scan is cheaper than millions of random index-guided fetches. This is why an index you added "just in case" sometimes goes unused: on that column, scanning is genuinely faster.
When NOT to index
- Small tables - a few hundred rows fit in a page or two; scanning is faster than index overhead.
- Low-selectivity columns on their own - a plain index on a two-value flag rarely helps (a partial index on the rare value can).
- Write-heavy columns with no read benefit - every index must be maintained on
INSERT,UPDATE, andDELETE, so redundant indexes tax every write. - Redundant prefixes - if you have
(a, b, c)you usually do not also need(a)or(a, b); the composite already covers those prefixes.
Index maintenance and fragmentation
Indexes decay as data changes. Splits, deletes, and updates leave pages partially full and out of physical order - fragmentation - so the same logical range needs more page reads over time. PostgreSQL additionally leaves dead tuples that VACUUM reclaims and can bloat indexes until a REINDEX. The maintenance loop is simple: monitor fragmentation and bloat, rebuild or reorganize when it crosses a threshold, and keep statistics fresh so the optimizer keeps costing plans correctly.
-- PostgreSQL: rebuild a bloated index without long locks
REINDEX INDEX CONCURRENTLY ix_orders_cust_date;
-- SQL Server: rebuild vs the lighter reorganize
ALTER INDEX ix_orders_cust_date ON orders REBUILD;
ALTER INDEX ix_orders_cust_date ON orders REORGANIZE;Writing sargable queries
A predicate is sargable (Search ARGument able) when the engine can use an index to satisfy it. The single most common self-inflicted performance wound is writing a filter that looks indexable but forces a full scan because the column is wrapped, converted, or negated. The fix is almost always to move the work off the column and onto the constant.
Functions on the indexed column
-- wrapping the column defeats the index on created_at
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;
-- so does a function on the left side of the comparison
SELECT * FROM users
WHERE LOWER(email) = 'sam@site.com';-- rewrite as a range so the B-tree can seek
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';
-- or index the expression itself (functional index)
CREATE INDEX ix_users_email_lc
ON users (LOWER(email));Leading wildcards
A B-tree is sorted left to right, so it can seek a known prefix but not a known suffix. LIKE 'abc%' uses the index; LIKE '%abc' cannot and scans the table. If you truly need suffix or substring search, that is a job for a full-text or trigram index, not a plain B-tree.
SELECT * FROM products
WHERE sku LIKE '%-2026'; -- leading % = full scanSELECT * FROM products
WHERE sku LIKE 'WIDGET-%'; -- anchored prefix = index seekImplicit conversion
When you compare a column to a value of a different type, the engine converts one side to match - and if it converts the column, the index is dead. Comparing an indexed VARCHAR account number to a numeric literal is the classic trap.
-- account_no is VARCHAR; the engine casts the column to int
SELECT * FROM accounts WHERE account_no = 100457;-- match the literal's type to the column's
SELECT * FROM accounts WHERE account_no = '100457';OR versus UNION
An OR across two different columns often stops the optimizer using an index on either, because no single index covers both branches. Splitting into a UNION (or UNION ALL when the branches cannot overlap) lets each branch use its own index. Many engines do this internally as an "index OR" or bitmap combination, but rewriting explicitly is a reliable win when they do not.
SELECT * FROM tickets
WHERE assignee_id = 88 OR reporter_id = 88;SELECT * FROM tickets WHERE assignee_id = 88
UNION
SELECT * FROM tickets WHERE reporter_id = 88;Join performance
Joins are where big queries live or die. The optimizer picks both a join order (which table drives) and a join algorithm, and understanding the three algorithms tells you what indexes to provide. Our SQL joins guide covers the join types themselves; here we focus on their cost.
| Algorithm | How it works | Fast when |
|---|---|---|
| Nested loop | For each outer row, look up matches in the inner table | Outer side is small and the join key on the inner side is indexed |
| Hash join | Build a hash table on the smaller input, probe it with the larger | Large, unindexed inputs joined on equality |
| Merge join | Walk two inputs already sorted on the join key in lockstep | Both inputs are already sorted (e.g. by index) on the key |
The failure mode to recognize is a nested loop over a large outer input with no index on the inner key. That is O(rows × rows) - it re-scans the inner table for every outer row and turns a one-second query into a five-minute one. The plan shows it as an inner scan with a huge loops count (recall loops=48211 from section 2). The fix is almost always the same: index the join key.
-- always index the columns you join on, especially FKs
CREATE INDEX ix_order_items_order
ON order_items (order_id);
-- with this, the engine can nested-loop cheaply
-- or merge-join if both sides are sorted on order_id
SELECT o.id, SUM(i.qty * i.price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= '2026-06-01'
GROUP BY o.id;Avoiding fan-out
Joining one row to many multiplies rows - fan-out. Join an order to its items and then to its shipments, and a single order can explode into dozens of rows before any aggregation, inflating every downstream sort and SUM. Two disciplines keep it in check: aggregate each one-to-many relationship in a subquery before joining it to the next, and never SUM across a join that has already fanned out (you will double-count). When a report needs totals from two different child tables, aggregate each separately and join the summaries, not the raw rows.
Rule of thumb: you cannot force a join algorithm portably, and you rarely should. Give the optimizer indexed join keys and fresh statistics, and it will choose nested-loop, hash, or merge correctly on its own. When it picks wrong, the cause is usually a bad row estimate - fix that, not the algorithm.
Reducing the work the engine does
The cheapest row to process is the one you never read. Before reaching for indexes or hardware, cut the sheer volume of data the query moves.
Select only the columns you need
SELECT * drags every column across the wire, defeats covering indexes (the index no longer contains everything the query needs), and bloats sorts and network transfer. Name the columns you actually use - it is the difference between an index-only scan and a table lookup per row.
Filter early
Push predicates as close to the scan as possible so rows are discarded before expensive joins and aggregations run over them. Filtering in a WHERE before a GROUP BY is far cheaper than a HAVING that filters after aggregating millions of rows. Modern optimizers push predicates down automatically when they safely can, but a subquery or non-sargable predicate can block them - so write the filter where it belongs.
Pagination: keyset vs OFFSET
LIMIT ... OFFSET n is a trap at scale. To return page 5,000 the engine must generate and discard every one of the preceding rows - the deeper the page, the slower it gets. Keyset (seek) pagination remembers the last row seen and continues past it with a sargable WHERE, so every page is equally fast.
-- reads and throws away 100,000 rows to show 20
SELECT id, title FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;-- carry the last seen key; seeks straight to the next page
SELECT id, title FROM posts
WHERE created_at < '2026-06-01 09:14:00'
ORDER BY created_at DESC
LIMIT 20;Avoiding N+1
The N+1 problem is an application-side pattern that a database cannot fix for you: fetch a list of 100 orders (1 query), then loop and fetch each order's items (100 more queries). The network round-trips dominate, and the database sees a storm of tiny statements. Collapse it into a single set-based query with a JOIN or an IN list, or use your ORM's eager-loading. One query returning 100 orders with their items beats 101 queries every time.
Statistics and cardinality estimation
Everything in section 1 hinges on statistics, so they deserve their own treatment. The optimizer keeps a statistical summary of each table: total row count, the number of distinct values per column (which drives selectivity), null fractions, and histograms that describe how values are distributed across ranges. From these it estimates how many rows each predicate and join will produce.
When statistics are stale - after a bulk load, a big delete, or steady growth - estimates drift from reality and the optimizer makes decisions for a table that no longer exists. It picks a nested loop expecting 10 rows when 10 million will flow through. That estimate-vs-actual gap in the plan is the fingerprint of stale stats, and refreshing them is often a one-line fix that beats any index change.
-- PostgreSQL
ANALYZE orders;
ANALYZE; -- whole database
-- SQL Server
UPDATE STATISTICS orders WITH FULLSCAN;
-- MySQL / InnoDB
ANALYZE TABLE orders;Two subtleties bite even experienced teams. First, most engines assume columns are independent: given city = 'Paris' AND country = 'France', they multiply the two selectivities and badly underestimate, because the columns are correlated. PostgreSQL's CREATE STATISTICS and SQL Server's multi-column stats exist to fix exactly this. Second, autovacuum/auto-update thresholds are proportional to table size, so very large tables can go a long time between automatic refreshes - schedule manual stats updates after big data movements rather than trusting the defaults.
Aggregation and sorting cost
Sorting and grouping are among the most expensive operations a query can do, because they may need to buffer the entire input. When the data does not fit in the memory budget (PostgreSQL's work_mem, SQL Server's memory grant), the operation spills to disk - and a disk sort is dramatically slower than an in-memory one. The plan tells you: PostgreSQL prints Sort Method: external merge Disk: 8432kB; SQL Server raises a sort or hash spill warning.
Using indexes to avoid sorts
The elegant fix is to not sort at all. A B-tree is already sorted, so if an index exists on the ORDER BY (or GROUP BY) columns in the right direction, the engine reads rows in order and skips the sort entirely. This is where composite index order from section 3 pays a second dividend: (customer_id, created_at) serves both the WHERE customer_id = ? filter and the ORDER BY created_at with no sort node in the plan.
-- BEFORE: explicit, possibly-spilling sort
Sort (actual rows=48211 loops=1)
Sort Method: external merge Disk: 84320kB
-> Seq Scan on orders
-- AFTER: index supplies order, sort node gone
Index Scan using ix_orders_cust_date on orders
(actual rows=48211 loops=1)The same principle helps aggregation. A GROUP BY can use an index to do a streaming group aggregate (read pre-sorted, emit groups as boundaries pass) instead of building a hash table over the whole input. And SELECT MAX(created_at) on an indexed column is answered instantly by reading one end of the index rather than scanning the table. For the deep version of this material, our performance tuning walkthrough works a full example end to end.
Caching, materialized views, and summary tables
The fastest query is the one you never run. When a result is expensive to compute but changes slowly, compute it once and serve the stored answer.
Result and application caching
Cache expensive, rarely-changing results in the application layer - Redis or Memcached - keyed by the query parameters, with a sensible TTL. A dashboard that recomputes the same aggregate every page load is a prime candidate. The trade-off is staleness and invalidation: cache only what tolerates being a few seconds or minutes old, and have a clear rule for when to evict.
Materialized views
A materialized view stores the physical result of a query and can itself be indexed. Unlike a plain view (which is just a saved query re-run every time), a materialized view is precomputed - ideal for heavy reporting aggregates. The cost is freshness: it must be refreshed, and until then it is stale.
-- PostgreSQL: precompute a daily sales rollup
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
COUNT(*) AS orders,
SUM(total) AS revenue
FROM orders
GROUP BY 1;
-- refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;Summary tables
When you need real-time-ish rollups on a huge table, a maintained summary table beats recomputing from scratch: keep a running total updated incrementally as new rows arrive (via triggers, a scheduled job, or the write path), so the dashboard reads a tiny pre-aggregated table instead of scanning millions of source rows. This is the standard pattern behind fast analytics on large fact tables - see working with large databases for how it fits into a broader big-data strategy.
Partitioning and archiving large tables
Past a certain size, a single physical table becomes the bottleneck no index can fully rescue. Partitioning splits one logical table into many physical pieces by a key - usually a date range - so the engine can prune whole partitions a query does not need and never read them.
-- PostgreSQL: range-partition orders by month
CREATE TABLE orders (
id bigint, customer_id int,
total numeric, created_at timestamptz
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_07 PARTITION OF orders
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');Partitioning pays off in three ways beyond pruning. Maintenance gets cheaper because you rebuild or vacuum one partition at a time. Dropping old data becomes instant - detaching a monthly partition is a metadata operation, whereas DELETE of millions of rows is slow and floods the write-ahead log. And each partition's indexes stay small enough to stay cached. The catch: partitioning only helps queries that filter on the partition key, it adds admin overhead, and a wrong key makes things worse. Reserve it for genuinely large tables.
Even without partitioning, archiving keeps hot tables fast. Move rows the application rarely reads - orders closed three years ago, expired sessions - into a history table or cold storage, so everyday queries scan a small warm working set. Do the move in bounded batches to avoid one giant table-locking transaction.
-- archive in chunks, not one giant statement
WITH moved AS (
DELETE FROM orders
WHERE created_at < '2023-01-01'
LIMIT 10000
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
-- repeat until zero rows moveMonitoring: finding slow queries
You cannot tune what you have not found. Before optimizing anything, rank queries by total impact - calls times average time - because a fast query run a million times often costs more than a slow one run twice. Every engine ships a way to surface this.
-- PostgreSQL: top offenders by cumulative time
SELECT substr(query, 1, 60) AS q,
calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;-- MySQL: enable and read the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- seconds
-- or query the digest table:
SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
-- SQL Server: Query Store or the ring buffer
SELECT TOP 10 qt.text, qs.execution_count,
qs.total_worker_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;Wait analysis, briefly
When individual queries look fine but the system still drags, the bottleneck is elsewhere - and wait statistics tell you where. Every engine tracks what sessions are waiting on: disk I/O, locks, latches, CPU scheduling, or network. High lock waits point at concurrency and long transactions; high I/O waits point at missing indexes or an undersized buffer pool; high CPU signals genuine compute or missing indexes forcing scans. SQL Server exposes sys.dm_os_wait_stats, PostgreSQL surfaces wait events in pg_stat_activity.wait_event. Read the top wait category first - it tells you which chapter of this guide to open.
Biggest wins and symptom → cause → fix
Not every lever pays equally. Across typical workloads, correcting indexes and rewriting queries return far more than hardware. Spend your time top-down.
These are rules of thumb, not laws - your baseline decides the truth. But the shape holds almost everywhere: indexing and query work dominate, and hardware is the last resort. Use the table below to jump from a plan symptom to its likely cause and the fix that addresses it.
| Symptom | Likely cause | Fix |
|---|---|---|
| Seq/full scan on a filtered big table | No usable index on the filter column | Add a B-tree or composite index on the predicate |
| Index exists but the plan ignores it | Non-sargable predicate (function/cast on the column) | Rewrite as a range; match literal types |
| Estimated rows far from actual rows | Stale or missing statistics | Run ANALYZE / UPDATE STATISTICS |
Nested loop with a huge loops count | Missing index on the inner join key | Index the join key; refresh stats |
| Sort or hash spills to disk | Sorting too many rows / low work memory | Add a matching index; filter earlier; raise work_mem |
| Deep pages get slower and slower | Large OFFSET discarding rows | Switch to keyset pagination |
| Same query fast sometimes, slow others | Parameter sniffing / cached plan reuse | Recompile hint or optimize for the common value |
| Many tiny identical queries per request | N+1 in the application / ORM lazy loading | Eager-load with a JOIN or IN list |
| Report totals double-counted | Fan-out from a one-to-many join | Aggregate each child in a subquery before joining |
| Whole huge table slow despite indexes | Table too large for one physical structure | Partition by date/key; archive cold rows |
That is the full conceptual map. When you are ready to act, run the companion performance tuning checklist top to bottom, and if you are chasing a SQL Server issue in particular, our guide to SQL Server performance problems and how to fix them gets specific. To build these instincts systematically, the SQL advanced course works through plans, indexes, and rewrites hands-on - and if you would rather hand it off, an experienced SQL developer can profile and fix a slow database in a fraction of the time.