Database Indexing Explained: Why Your Query Is Slow and How to Actually Fix It | Blog

# Database Indexing Explained: Why Your Query Is Slow and How to Actually Fix It

TL;DR: Your query is slow because Postgres is reading every row in the table (a sequential scan) instead of jumping straight to the rows that match — and the fix is almost always a well-chosen B-tree index on the columns in your WHERE, JOIN, or ORDER BY clauses, verified with EXPLAIN ANALYZE rather than guessed at.

Indexing is one of those topics every developer nods along to and few actually reason about correctly. This piece walks through what an index actually is, reads a real EXPLAIN ANALYZE plan before and after adding one, and covers the mistakes that make indexes useless even when they exist.

What an index actually is

A database index is a separate, ordered data structure that stores a copy of one or more columns' values alongside a pointer back to the full row. It exists so the database can find rows without scanning the entire table.

The default and most common index structure — in PostgreSQL, MySQL/InnoDB, SQL Server, and most relational databases — is the B-tree (balanced tree). PostgreSQL's documentation is explicit that CREATE INDEX builds a B-tree unless you tell it otherwise, because B-trees "fit the most common situations" (PostgreSQL 18 docs: Index Types).

A B-tree keeps values in sorted order across a tree of fixed-size pages, so a lookup costs roughly O(log n) page reads instead of O(n) row scans. Concretely, that's why a B-tree index handles:

Equality lookups (WHERE user_id = 42)
Range queries (WHERE created_at > '2026-01-01')
Sorting (ORDER BY created_at)
IS NULL / IS NOT NULL checks

...but it does not efficiently handle full-text search, substring matching (LIKE '%term%'), or arbitrary set/array containment — those need GIN, GiST, or other specialized index types, which is a separate topic.

A real before/after with EXPLAIN ANALYZE

Here's a concrete example. Assume a table of ~5 million rows:

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  total_cents integer NOT NULL
);

We want the query a support dashboard runs constantly:

SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 8842123
  AND status = 'refunded'
ORDER BY created_at DESC
LIMIT 20;

Before: no index on `customer_id`/`status`

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 8842123 AND status = 'refunded'
ORDER BY created_at DESC
LIMIT 20;

 Limit  (cost=98421.33..98421.38 rows=20 width=24) (actual time=612.104..612.109 rows=14 loops=1)
   Buffers: shared hit=41112 read=39028
   ->  Sort  (cost=98421.33..98421.58 rows=98 width=24) (actual time=612.102..612.104 rows=14 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 26kB
         ->  Seq Scan on orders  (cost=0.00..98418.75 rows=98 width=24)
                                 (actual time=0.038..611.812 rows=14 loops=1)
               Filter: (customer_id = 8842123 AND status = 'refunded')
               Rows Removed by Filter: 4999986
               Buffers: shared hit=41112 read=39028
 Planning Time: 0.312 ms
 Execution Time: 612.147 ms

Postgres reads all ~5 million rows (Seq Scan), throws away nearly all of them (Rows Removed by Filter: 4999986), and touches ~80,000 buffer pages to find 14 matching rows. 612ms for a query that returns 14 rows is exactly the "slow query" symptom that shows up in APM tools.

After: a composite B-tree index

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Column order matters here and isn't arbitrary. Per the PostgreSQL manual, equality conditions on leading columns are what actually narrow the portion of the index scanned, so the equality-filtered columns (customer_id, status) go first, and the column used for ordering (created_at) goes last (PostgreSQL 18 docs: Multicolumn Indexes).

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 8842123 AND status = 'refunded'
ORDER BY created_at DESC
LIMIT 20;

 Limit  (cost=0.56..8.71 rows=14 width=24) (actual time=0.041..0.058 rows=14 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using idx_orders_customer_status_created on orders
         (cost=0.56..8.71 rows=14 width=24) (actual time=0.040..0.054 rows=14 loops=1)
         Index Cond: (customer_id = 8842123 AND status = 'refunded')
         Buffers: shared hit=6
 Planning Time: 0.198 ms
 Execution Time: 0.081 ms

Same result set, 6 buffer pages instead of ~80,000, and execution time drops from 612ms to under 0.1ms — roughly a 7,500x reduction on this dataset. The plan also no longer needs a separate Sort step: because the index already stores rows in (customer_id, status, created_at DESC) order, Postgres can walk the index directly in the order the query needs.

Two notes on reproducing this yourself: BUFFERS is included by default with EXPLAIN ANALYZE starting in PostgreSQL 18 — on 17 and earlier you need to add it explicitly, as in the examples above (depesz: Enable BUFFERS with EXPLAIN ANALYZE by default). And your own numbers will vary by hardware, cache state, and data distribution — treat the shape of the plan (Seq Scan → Index Scan, buffer counts, absence of a Sort node) as the thing to verify, not the exact milliseconds.

Reading a plan without guessing

A few fields do most of the diagnostic work:

Field	What it tells you
`Seq Scan` vs `Index Scan` / `Index Only Scan`	Whether the planner used an index at all
`actual time=X..Y`	Y is when the node finished producing all its rows — compare it to the top node's total
`rows=N` (actual vs. estimated)	A large gap means stale statistics; run `ANALYZE table_name`
`Rows Removed by Filter`	Rows read then discarded — a strong signal a better index would help
`Buffers: shared hit/read`	Pages served from cache vs. read from disk — `read` under load is real I/O cost

If EXPLAIN ANALYZE shows a Seq Scan on a large table for a query filtering on a handful of rows, that's the primary signal to add an index. If it shows an Index Scan but the planner's row estimate is wildly off from the actual count, the fix is usually ANALYZE, not a new index.

Why an index sometimes doesn't get used

This trips people up constantly: adding an index doesn't guarantee the planner will use it.

Low selectivity. An index on a status column with only 3 possible values, queried on the value that matches 60% of rows, is often skipped in favor of a sequential scan — reading the index plus the table would cost more than just reading the table.
Functions on the indexed column. WHERE lower(email) = 'x' won't use a plain index on email; you need an expression index: CREATE INDEX ON users (lower(email)).
Implicit type mismatches. Comparing a text column to a numeric literal, or an untyped parameter, can silently defeat index usage in some drivers/ORMs.
Stale statistics. The planner estimates selectivity from pg_statistic, populated by ANALYZE (usually run automatically by autovacuum). Right after a large bulk load, stats can lag until autovacuum catches up.
Small tables. For a table that fits in a handful of pages, a sequential scan is often genuinely cheaper than an index scan — this is correct planner behavior, not a bug.

Practical guidance

Index columns used in WHERE, JOIN ON, and ORDER BY — not every column "just in case." Every index adds write overhead (it must be updated on every INSERT/UPDATE/DELETE) and disk space.
For composite indexes, order columns equality-first, then range/sort columns, per the planner logic above.
Consider a covering index with INCLUDE when a query only needs a couple of extra columns beyond the filter — this can enable an index-only scan, which skips the table heap entirely as long as the relevant pages are marked all-visible in the visibility map (PostgreSQL 18 docs: Index-Only Scans and Covering Indexes).
Always confirm with EXPLAIN ANALYZE on production-representative data. Query plans depend on table size and data distribution, so a plan that looks fine on a 10,000-row staging database can fall apart at 10 million rows in production.
Watch write-heavy tables for index bloat and over-indexing — pg_stat_user_indexes will show indexes with near-zero scans that are pure write overhead and safe to drop.

Getting this right on a handful of hot queries is usually a few hours of work. If your team is dealing with a broader pattern of slow queries across a legacy schema, or is scoping a data model from scratch and wants indexing strategy baked in from day one, that's the kind of structural work our custom software development team handles alongside application code.

FAQ

Does adding an index make writes slower?

Yes, to a degree. Every INSERT, UPDATE, or DELETE that touches an indexed column has to update the index structure too. For most OLTP workloads a handful of well-chosen indexes is a clear net win, but indexing every column "for safety" measurably slows down write throughput.

How many indexes are too many on one table?

There's no fixed number — it depends on write volume and index size. The practical check is pg_stat_user_indexes: if idx_scan is 0 or near-0 for an index that's existed through a normal traffic cycle, it's costing you writes and disk space for no read benefit, and is a candidate to drop.

Why does EXPLAIN show a Seq Scan even though I have an index?

Usually one of: the query isn't selective enough for the index to be cheaper than a full scan, a function or type cast is applied to the indexed column, table statistics are stale (run ANALYZE), or the table is small enough that a sequential scan is genuinely faster — all covered above.

Is a B-tree always the right index type?

For equality, range, and sort operations, yes — it's the default for good reason. For full-text search use GIN with tsvector, for JSONB containment queries use GIN, and for geometric or nearest-neighbor queries use GiST. Picking B-tree by default and reaching for a specialized type only when the query pattern calls for it is the right order of operations.