Soumitra SahaMay 20, 2025

Database Indexing: Why Your Queries Get Slow

I walk through how PostgreSQL B-tree indexes work, how to read EXPLAIN ANALYZE to confirm what the planner actually does, and the indexing mistakes that cancel out the gains.

Your API endpoint was fast six months ago. The same query, the same code, the same server — but now it takes four seconds to respond. The table grew, and PostgreSQL is reading every row to find the handful that match your WHERE clause.

This is the sequential scan problem, and it shows up in almost every production system that lives long enough. An index fixes it by giving the database a shortcut: a sorted structure it can traverse in a few jumps instead of reading the whole table. But adding an index the wrong way — on the wrong column, in the wrong order, without understanding how the planner decides to use it — can leave you with slower writes and no improvement to reads.

This post covers how B-tree indexes work in PostgreSQL, when the planner chooses to use them, how to confirm it with EXPLAIN ANALYZE, and the indexing mistakes that are easy to make once you think you understand the basics. If you’re building a REST API that queries user rows, this is what separates a four-second endpoint from a sub-second one.

Series: Part 3 of 4. Start with Part 1: PostgreSQL for Developers

What a database index actually is

Without an index, a query with a WHERE clause forces PostgreSQL to scan the entire table from beginning to end — every row, every page on disk. This is a sequential scan, and it’s efficient when you need most of the rows. When you need only a few rows out of a million, it’s expensive.

An index is a separate data structure that stores column values in sorted order alongside pointers back to the original rows. When PostgreSQL uses an index, it walks the sorted structure to find matching values and fetches only those rows directly. Instead of reading a million rows, it reads a handful of index pages and then fetches the target rows.

Creating an index on a column takes one statement:

CREATE INDEX orders_user_id_idx ON orders (user_id);

PostgreSQL will automatically consider this index for queries that filter, join, or sort on user_id — when it estimates that doing so is cheaper than scanning the whole table. The planner always makes that decision based on cost estimates, not fixed rules. That distinction matters more than most people expect.

How B-tree indexes work

B-tree (balanced tree) is PostgreSQL’s default index type. When you run CREATE INDEX without specifying a type, you get a B-tree.

A B-tree is a self-balancing search tree. Each node holds sorted values. The root points to interior nodes, interior nodes point to leaf nodes, and leaf nodes store the indexed values alongside the physical location of the matching row on disk — the heap pointer. A lookup descends from root to leaf in a few hops, reading only the path to your answer rather than the whole structure.

According to the PostgreSQL documentation on index types, B-tree indexes handle these comparison operators directly: <, <=, =, >=, >. They also work with BETWEEN, IN, IS NULL, and IS NOT NULL. For LIKE patterns, a B-tree index helps only when the pattern is anchored at the start of the string — col LIKE 'foo%' can use an index, but col LIKE '%bar' cannot.

One useful side effect: because a B-tree stores entries in sorted order, the planner can use the index to satisfy an ORDER BY clause without a separate sort step. For queries that only need the first few rows — combined with LIMIT — this often matters.

When PostgreSQL decides not to use an index

This is the question that trips people up most often. You add an index, run the query, and the planner still does a sequential scan. There are a few common explanations.

Low selectivity. The planner estimates how many rows a condition will return. If the estimate is high — typically above 10 to 20 percent of the table — a sequential scan is often faster. Reading rows through an index requires random I/O: the planner jumps to different parts of disk for each row location the index returns. For a large fraction of the table, sequential heap reads beat that many random jumps.

A column with only two distinct values — a boolean or a two-value status enum — often falls here. If half the rows have status = 'active', PostgreSQL will typically skip an index on status and scan the heap directly. The documentation notes this explicitly: a query searching for a common value will not use the index anyway.

The table is small. If the table fits on one or two disk pages, the planner knows it will read those pages regardless. No index makes that cheaper.

Stale statistics. PostgreSQL estimates row counts from statistics gathered by ANALYZE. After a large bulk insert, those statistics may still reflect the old row count. The planner will make poor decisions until you run ANALYZE orders to refresh them. In a busy production database, autovacuum handles this automatically; after a manual bulk load, run it yourself.

Type mismatch. If your indexed column is integer and your query uses a string literal — WHERE user_id = '42' — PostgreSQL may skip the index due to type coercion. Match the query type to the column type.

Reading EXPLAIN ANALYZE output

EXPLAIN shows the query plan the planner chose. EXPLAIN ANALYZE runs the query and adds the real execution numbers alongside the estimates. Use EXPLAIN ANALYZE any time you need to confirm whether a query actually uses an index and how long each step really takes.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Before adding an index, output might look like this:

Seq Scan on orders  (cost=0.00..4850.00 rows=237 width=96)
                    (actual time=0.041..52.1 rows=237 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 99763
Planning Time: 0.8 ms
Execution Time: 52.2 ms

After CREATE INDEX orders_user_id_idx ON orders (user_id):

Index Scan using orders_user_id_idx on orders
  (cost=0.42..561.27 rows=237 width=96)
  (actual time=0.031..0.87 rows=237 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.5 ms
Execution Time: 0.95 ms

Reading those numbers:

Field	Meaning
`cost=0.00..4850.00`	Planner’s start-up cost and total cost, in arbitrary units
`rows=237`	Planner’s estimated row count
`actual time=0.041..52.1`	Real execution time in milliseconds
`loops=1`	How many times this node ran
`Rows Removed by Filter: 99763`	The scan read all rows and discarded 99,763 of them

That last line is the clearest signal of a missing index. The scan read 100,000 rows to return 237.

The node type tells you the scan strategy: Seq Scan is a full table read, Index Scan uses the index directly, and Bitmap Index Scan collects row locations from the index first and then fetches them — common when many rows match. According to the PostgreSQL EXPLAIN documentation, actual time is in real milliseconds and cost is in arbitrary planner units — the two aren’t directly comparable.

Try this

Open a psql session connected to your database.

Run EXPLAIN ANALYZE SELECT * FROM some_table WHERE some_column = some_value; on a table with at least 10,000 rows.

Look at the first line of the output for the scan node type.

Expected result: If you see Seq Scan with a large Rows Removed by Filter count, that column is a good candidate for an index.

Check this before moving on

You can distinguish Seq Scan, Index Scan, and Bitmap Index Scan in the output
You know that actual time is in real milliseconds and cost is in planner units — the two are not comparable
You understand that Rows Removed by Filter means those rows were read from disk and then discarded, not filtered before the scan

Indexing mistakes that hurt more than they help

Indexing every column. An index speeds up reads but slows down writes. Every INSERT, UPDATE, or DELETE must update every index on the table. A table with ten indexes has noticeably slower writes, and each index consumes storage. Add indexes only for columns that actually appear in WHERE, JOIN, or ORDER BY clauses in your frequent queries.

Indexing low-cardinality columns. A boolean column has two values. A status column with two or three options has two or three values. An index on either is usually pointless for common values, because PostgreSQL will determine that fetching 40% or 50% of the table via random index jumps costs more than a sequential scan. The index consumes storage and slows every write for no read benefit.

Forgetting ANALYZE after bulk inserts. After loading a large dataset, the planner’s statistics still reflect the old state. Run ANALYZE table_name to update them. Without fresh statistics, the planner may choose a sequential scan when an index would win — not because the index is wrong, but because the planner doesn’t know the table grew.

Composite indexes and the column order rule

A composite index spans multiple columns. The key rule from the PostgreSQL multicolumn index documentation: a multicolumn B-tree index is most efficient when there are constraints on the leading (leftmost) columns.

CREATE INDEX orders_user_date_idx ON orders (user_id, created_at);

This index efficiently serves queries that filter on user_id alone or on both user_id and created_at:

SELECT * FROM orders WHERE user_id = 42 AND created_at > '2025-01-01';
SELECT * FROM orders WHERE user_id = 42;

This index doesn’t efficiently serve a query that filters only on created_at:

SELECT * FROM orders WHERE created_at > '2025-01-01';

For that query, a separate index on created_at alone is better.

A practical heuristic: put equality conditions first, range conditions second. If your query filters on user_id = constant and then on created_at > date, put user_id first in the index definition. The leading column is the one that narrows the search the most.

The documentation also warns to use multicolumn indexes sparingly — a single-column index is sufficient in most cases. Indexes with more than three columns are rarely useful.

Partial indexes: smaller and more precise

A partial index only covers the rows that satisfy a predicate. The result is a smaller, faster index with less maintenance overhead.

CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status = 'pending';

If your application queries pending orders heavily and there are far fewer pending orders than completed ones, this index is much more compact than a full index on created_at. Smaller means faster to scan, cheaper to update, and more likely to fit in memory.

For PostgreSQL to use a partial index, the query’s WHERE clause must logically imply the index predicate. This query uses the index:

SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01';

This one doesn’t — the predicate isn’t satisfied:

SELECT * FROM orders WHERE created_at > '2025-01-01';

Partial indexes work particularly well for task queues (index only unprocessed items), audit logs (index only recent rows), and any table where a small active subset is queried constantly while most of the data is historical and rarely accessed.

The right way to approach indexing isn’t to ask “which columns could I index?” It’s to run EXPLAIN ANALYZE on your slowest queries, look at the scan type and the row counts, and build only the indexes the evidence tells you to build. An index added for the wrong reason doesn’t just fail to help — it slows down every write from that point on.