SQL Indexes and Query Plans
Learn when database indexes help, how composite index order works, and how to inspect query plans safely with EXPLAIN.
Indexes can help a database find rows without scanning an entire table, but they also use storage and add work to writes. This practical guide explains when an index may help, how composite indexes work, how to inspect a PostgreSQL query plan, and why an index scan is not always the fastest choice.
What an index does
An index is a separate data structure that helps the database locate rows matching a query. Instead of checking every row, the database may use an index to narrow candidates and then fetch matching records. Many systems use B-tree indexes for common equality and range lookups, while other index types suit different data and operators.
Indexes are not free. They consume disk and memory, and inserts, updates, and deletes may need to maintain each affected index. A table with many unnecessary indexes can make writes slower and increase storage and maintenance costs.
Start with the query, not a guess
Find a real, frequent, slow query and inspect its filters, joins, sort order, and result size. For example, an orders table might commonly fetch one customer's newest orders:
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;An index beginning with customer_id and then created_at may be worth evaluating for this access pattern. Whether the planner uses it depends on the database, statistics, table size, selectivity, query shape, and cost estimates. Do not assume that adding an index guarantees a faster query.
Understand composite index order
A composite index stores keys from more than one column in a defined order. Its leading columns matter: an index on (customer_id, created_at) is suited to lookup patterns that constrain customer_id and may also help with ordering by created_at. It generally does not serve a query filtering only by created_at as directly as an index that begins with that column. Exact optimizer behavior varies by database and index type.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);Before creating a similar index, check whether a primary key, unique constraint, or existing composite index already covers the workload. Use production-like data distributions where possible, and measure both read and write effects.
Inspect the plan with EXPLAIN
In PostgreSQL, EXPLAIN displays the plan chosen by the planner. It can show sequential scans, index scans, joins, sorts, estimated row counts, and estimated costs. Compare estimated rows with actual workload knowledge; poor estimates can lead to a poor plan.
EXPLAIN
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;For actual timing and row counts, PostgreSQL's EXPLAIN ANALYZE executes the statement and reports measured plan details. Start with a read-only SELECT when learning. With data-changing statements, ANALYZE really performs the changes; use a safe transaction or a non-production environment when experimenting. Planner costs are estimates, not milliseconds, and timings vary with cache state and system load.
Why a sequential scan can be correct
An index is most helpful when it can avoid reading a large share of the table. If a query returns a large fraction of rows, reading the table sequentially may cost less than looking up many index entries and then fetching rows individually. A small table may also be cheaper to scan directly. A visible sequential scan is not automatically a bug.
Indexes may not help when a condition transforms the indexed column in a way the optimizer cannot match to that index, when the compared types or collations differ, or when the query needs most columns and rows. Some databases support expression or generated-column indexes for specific cases; choose these only after reviewing the engine's rules and the query plan.
Practical tuning workflow
- Capture the exact SQL, representative parameters, table sizes, and the performance symptom.
- Read the execution plan and note scans, estimated versus actual rows, sorts, and repeated loops.
- Check table statistics and whether existing indexes match the query's leading filters and ordering.
- Test one focused index change on representative data; compare latency and resource use.
- Check write-heavy workloads too, because every extra index has a maintenance cost.
- Keep or remove the index based on measured workload benefit, and document why it exists.
Database engines differ in supported index types, planner behavior, online index creation, and diagnostic syntax. Use the manual for your exact engine and version. The general habit is stable: measure a query, understand its plan, test a targeted change, then measure the full workload again.
Further reading
Want a structured Java learning path?
Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.