An index is another way to find data
Without a useful index, a database may inspect many rows to find the ones matching a condition. An index maintains an additional structure that helps locate matching data efficiently. The analogy is a book index: looking up a topic is faster than reading every page.
The analogy has a limit. A database must keep its indexes synchronized when rows are inserted, updated, or deleted. Indexes occupy storage, consume memory, and add write work. They are a performance tradeoff, not a free acceleration switch.
How a B-tree helps
Many relational databases use B-tree-family structures for ordinary indexes. Values are organized in a balanced tree with sorted keys. The database can navigate through a relatively small number of pages to locate a key, then scan nearby keys for a range.
CREATE INDEX posts_published_at_idx
ON posts (published_at);
SELECT id, title
FROM posts
WHERE published_at >= '2026-09-01'
ORDER BY published_at DESC
LIMIT 20;
An index on published_at may help the filter and ordering in this query. Whether the optimizer chooses it depends on table size, estimated matching rows, statistics, and database-specific behavior.
The engine may still need to fetch the actual rows after finding index entries. Some indexes can cover all required columns, enabling an index-only or covering access path under the database's rules. In PostgreSQL, index-only scans also depend on visibility information, so having all columns in the index does not guarantee zero heap reads.
Composite indexes and column order
A composite index orders keys using more than one column:
CREATE INDEX posts_status_date_idx
ON posts (status, published_at DESC, id DESC);
SELECT id, title, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;
The status equality narrows the search, and the remaining key order matches the desired sequence. B-tree indexes are often most useful when filters constrain leading columns. Some engines support additional strategies such as skip scans, so treat rules of thumb as a starting point and inspect the actual plan.
An index on (status, published_at) is not equivalent to one on (published_at, status). Select column order around the queries you actually need to accelerate, including filters, ranges, joins, and ordering.
Selectivity changes the calculation
Selectivity describes how much of the table a condition returns. If a query returns almost every row, reading the table sequentially can be cheaper than navigating an index and repeatedly fetching rows.
A low-cardinality column such as a boolean is not automatically a useful standalone index. It can still be useful when one value is rare, in combination with other columns, or in a partial index. PostgreSQL, for example, supports indexes restricted to rows satisfying a predicate:
CREATE INDEX published_posts_date_idx
ON posts (published_at DESC, id DESC)
WHERE status = 'published';
This syntax and its exact planner behavior are database-specific. A partial index can reduce size and write overhead when only a subset matters, but queries must match conditions the optimizer can prove fit its predicate.
Read the execution plan
Use your database's EXPLAIN facilities to inspect how it plans to execute a query. Look for scans, join methods, sort operations, estimated row counts, and whether filtering discards large numbers of rows.
EXPLAIN
SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 20;
In PostgreSQL, EXPLAIN ANALYZE actually executes the query and reports observed timings and row counts. Use caution with write statements and expensive production workloads. Large differences between estimated and actual rows may point to stale statistics or correlations the optimizer models poorly.
Measure with representative data. A query that looks instant on a table of 100 rows may behave very differently with millions of rows or skewed values.
Avoid making the index unusable
Applying a function or incompatible conversion to an indexed column can prevent a normal index from matching the query efficiently:
-- Often easier to optimize with an ordinary timestamp index:
WHERE created_at >= '2026-10-01'
AND created_at < '2026-10-02'
This range can be preferable to extracting a date from every timestamp, though timezone semantics must match the intended calendar day. Expression indexes can support computed values where the database permits them.
A normal B-tree also does not solve every search problem. Leading-wildcard text matching, full-text search, spatial queries, and similarity search may need specialized indexes or search systems.
Pagination and maintenance
Large OFFSET values can make the database scan and discard many rows. Keyset pagination uses the last seen key instead:
SELECT id, title, published_at
FROM posts
WHERE status = 'published'
AND (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC
LIMIT 20;
This PostgreSQL example uses a unique tie-breaker for deterministic ordering. It is useful for sequential browsing but does not provide arbitrary page-number jumps. Concurrent updates can still change what a user sees, so choose appropriate consistency behavior.
Review unused and overlapping indexes as workloads evolve. Index creation can consume resources or block writes depending on the engine and method. Plan large changes carefully, monitor write latency, and confirm that the improvement matters to user-visible operations. The best index is one that serves an important query enough to justify its ongoing cost.