Database Indexes: Why Postgres Ignores the One You Added

Here is a query. It filters an email column. There is an index on that email column. The pattern is anchored to the front of the string, which is exactly the shape the PostgreSQL documentation says a B-tree index can serve.^[2] On a table of two million rows, it takes 45.6 milliseconds and does a full sequential scan.

Add one more index, differing from the first only in its operator class, and the same query takes 0.51 milliseconds. Nothing about the data changed. Nothing about the SQL changed. The first index was simply answering a question nobody asked.

That is the whole reason I wanted to write this. The standard advice for a slow query is “add an index,” and in my experience that advice is wrong more often than it is right, because the index is usually already there. What is missing is a query the planner can match to it.

What a B-tree actually is

PostgreSQL creates a B-tree by default, and B-trees fit most situations.^[2] The structure is a shallow tree of sorted pages. The root page holds a set of boundary values pointing at child pages, those point at further children, and the bottom layer holds the indexed values in order with pointers into the table. Finding a value means reading the root, picking one branch, and repeating. Three or four page reads gets you to any row in a table of tens of millions.

If you have read about how Big-O notation describes algorithmic cost, this is the classic O(log n) versus O(n) split made physical. The sequential scan reads everything. The index does not. The interesting part is that the constant factors are enormous here, because the unit is not a comparison, it is a disk page.

I ran this on a plain PostgreSQL 17 container with a two-million-row orders table. One customer lookup, before and after a single index on customer_id:

-- No index on customer_id
EXPLAIN ANALYZE SELECT id, total_cents FROM orders WHERE customer_id = 91827;

 Gather  (cost=1000.00..34717.77 rows=11 width=16) (actual time=7.400..38.926 rows=6 loops=1)
   ->  Parallel Seq Scan on orders  (cost=0.00..33716.67 rows=5 width=16) (actual time=3.841..29.613 rows=2 loops=3)
         Filter: (customer_id = 91827)
         Rows Removed by Filter: 666665
 Execution Time: 38.972 ms

-- CREATE INDEX orders_customer_id_idx ON orders (customer_id);

 Bitmap Heap Scan on orders  (cost=4.51..47.93 rows=11 width=16) (actual time=0.072..0.090 rows=6 loops=1)
   Recheck Cond: (customer_id = 91827)
   Heap Blocks: exact=6
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.51 rows=11 width=0) (actual time=0.059..0.059 rows=6 loops=1)
         Index Cond: (customer_id = 91827)
 Execution Time: 0.128 ms

Look at Rows Removed by Filter: 666665. That is one of three parallel workers throwing away two thirds of a million rows to find two. Six rows came back and the database read all two million to get them. With the index, Heap Blocks: exact=6: six pages of the table touched, total. Two orders of magnitude of wall-clock time, and the gap widens as the table grows.

The bill arrives on every write

The docs are blunt about the cost. After an index is created, the system has to keep it synchronized with the table, and this adds overhead to data manipulation operations.^[7] That sentence undersells it. I built two identical tables, gave one of them six indexes, and inserted the same 500,000 rows into each.

This is why “just add an index” is bad advice when it is offered reflexively. Indexes that are seldom or never used in queries should be removed.^[7] A table with nine indexes on it, seven of which the planner never picks, is a table whose writes are slow for no reason at all. Go look at pg_stat_user_indexes on your own production database sometime. I promise you will find at least one index with a scan count of zero and a size in the hundreds of megabytes.

Column order in a composite index decides whether it exists

Here is where most of the real-world confusion lives. An index on two columns is not two indexes. The rule PostgreSQL states is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will always be used to limit the portion of the index that is scanned, and constraints on columns to the right of these are checked in the index but do not necessarily reduce the portion that has to be scanned.^[1]

In plain terms: the index is sorted by the first column, then by the second within each value of the first. Skip the first column and the entries you want are scattered across the whole structure. That is the leftmost-prefix rule, and it is not a quirk. It falls straight out of what “sorted” means.

CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);

-- Leading column present: index used
EXPLAIN ANALYZE SELECT id FROM orders
  WHERE customer_id = 91827 AND created_at >= '2025-06-01';

 Index Scan using orders_cust_created_idx on orders  (actual time=0.058..0.058 rows=0 loops=1)
   Index Cond: ((customer_id = 91827) AND (created_at >= '2025-06-01 00:00:00+00'))
 Execution Time: 0.095 ms

-- Leading column absent: the same index is useless
EXPLAIN ANALYZE SELECT id FROM orders
  WHERE created_at >= '2025-11-25' AND created_at < '2025-11-26';

 Gather  (actual time=0.284..37.243 rows=2883 loops=1)
   ->  Parallel Seq Scan on orders  (actual time=0.098..27.717 rows=961 loops=3)
         Filter: ((created_at >= '2025-11-25 00:00:00+00') AND (created_at < '2025-11-26 00:00:00+00'))
         Rows Removed by Filter: 665706
 Execution Time: 37.343 ms

-- CREATE INDEX orders_created_idx ON orders (created_at);  ->  8.022 ms

The docs also warn that multicolumn indexes should be used sparingly, and that indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.^[1] I would go further. If you are on your fourth column, you are probably modeling a query, not a table, and that is a data architecture problem rather than an indexing one.

The three ways a query hides its own column from the index

Now the part that actually explains most slow queries in production. An index on email indexes the values of email. It does not index lower(email), because that is a different set of values. The moment you wrap the column in anything, the planner has nothing to match.

-- Index exists: CREATE INDEX orders_email_idx ON orders (email);

EXPLAIN ANALYZE SELECT id FROM orders WHERE lower(email) = 'user1337@example.com';

 Gather  (cost=1000.00..37800.00 rows=10000 width=8) (actual time=1.162..196.011 rows=1 loops=1)
   ->  Parallel Seq Scan on orders  (actual time=121.277..183.303 rows=0 loops=3)
         Filter: (lower(email) = 'user1337@example.com'::text)
 Execution Time: 196.068 ms

-- Fix A: rewrite the predicate so the bare column is on the left
EXPLAIN ANALYZE SELECT id FROM orders WHERE email = 'User1337@Example.com';
 Index Scan using orders_email_idx  ->  Execution Time: 0.090 ms

-- Fix B: index the expression itself
CREATE INDEX orders_lower_email_idx ON orders (lower(email));
 Index Scan using orders_lower_email_idx  ->  Execution Time: 0.128 ms

Notice the estimate in that first plan: rows=10000 against an actual of 1. The planner has no statistics for lower(email), so it fell back to a generic guess of 0.5% of the table. That mismatch between estimated and actual rows is itself the tell. When the two are wildly apart, the plan you got was chosen on bad information.

Expression indexes are the documented fix.^[3] They cost more to maintain, because the derived expression must be computed for each row insertion and non-HOT update, but they are not recomputed during an indexed search.^[3] Fine trade. The other two shapes fail for the same underlying reason.

A cast is a function with a friendlier syntax. Writing customer_id::text = '91827' against a bigint column produced a sequential scan at 60.1 milliseconds in my test, filtering on ((customer_id)::text = '91827'::text). That cast usually arrives from an ORM or a driver that bound a parameter as text, which is why it is so hard to spot by reading application code.

And LIKE has two separate traps. The obvious one is a leading wildcard: the optimizer can use a B-tree for LIKE only if the pattern is a constant anchored to the beginning of the string, so col LIKE 'foo%' is eligible and col LIKE '%bar' is not.^[2] The non-obvious one is the one that opened this article.

If your database does not use the C locale, you need a special operator class to support indexing of pattern-matching queries.^[4] The operator classes text_pattern_ops, varchar_pattern_ops and bpchar_pattern_ops compare values strictly character by character rather than by locale-specific collation rules, which is what makes them suitable for pattern matching.^[4] Adding CREATE INDEX ... (email text_pattern_ops) took my anchored prefix query from 45.6 milliseconds to 0.51 milliseconds. And the catch goes both ways: ordinary < and > comparisons cannot use the pattern operator classes, so a column you filter and sort on may genuinely need both indexes.^[4]

Covering indexes, and the scan that never touches the table

There is one more level. Normally an index scan finds the entries, then visits the table to fetch the columns you asked for. If the index already contains every column the query needs, PostgreSQL can skip that second step entirely and return values directly out of each index entry.^[5] That plan node is called an Index Only Scan.

You build one with the INCLUDE clause, which adds columns as payload rather than as part of the search key.^[5] The result on a 2,072-row range query:

-- Index on (customer_id, created_at): finds rows, then reads the table
 Bitmap Heap Scan on orders  (actual time=0.634..9.109 rows=2072 loops=1)
   Heap Blocks: exact=1974
 Execution Time: 9.293 ms

CREATE INDEX orders_cust_incl_idx ON orders (customer_id) INCLUDE (total_cents);

 Index Only Scan using orders_cust_incl_idx on orders  (actual time=0.034..0.137 rows=2072 loops=1)
   Index Cond: ((customer_id >= 5000) AND (customer_id <= 5200))
   Heap Fetches: 0
 Execution Time: 0.240 ms

Heap Fetches: 0 is the line to look for. It means the table was not read at all. Nearly 2,000 page visits became zero, and the query got about 38 times faster.

There is a condition, and it is the one people miss. PostgreSQL only trusts the index this way when the visibility map says every row on the corresponding heap page is old enough to be visible to all transactions.^[5] That map is maintained by vacuum. On a table being rewritten constantly, Heap Fetches creeps up and the advantage erodes, which is why the docs say there is little point in including payload columns unless the table changes slowly enough for the scan to avoid the heap.^[5] Read-mostly reporting tables: yes. A hot write queue: probably not. It behaves a lot like any other cache you maintain, in that the hit rate is the whole story.

Reading the plan well enough to tell

None of this is diagnosable by staring at SQL. You have to ask the database. EXPLAIN shows the plan it would use; EXPLAIN ANALYZE runs the query and reports what actually happened, so wrap data-modifying statements in a transaction and roll back.^[6] Four things to look at, in this order:

1. The scan node. Seq Scan means the whole table. Index Scan or Bitmap Index Scan means the index was used. Index Only Scan means the table was never touched. A Seq Scan on a small table is fine and often optimal; on a large one with a selective filter it is the bug.
2. Estimated rows against actual rows. The two numbers appear as rows=N in the cost section and rows=Nin the actual section. Costs are in arbitrary units determined by the planner's cost parameters and actual times are in milliseconds, so they will not match.^[6] The row counts should. When they are off by three orders of magnitude, the planner chose blind.
3. Rows Removed by Filter. Work done to produce nothing. A big number here next to a small result set is the signature of a predicate the index could not serve.
4. loops. An inner node with loops=10 ran ten times, and its reported time is per loop.^[6] This is where a genuinely fast node turns out to be the expensive part of the plan.

What to actually do on Monday

Run EXPLAIN ANALYZE on the slow query first. If you see a sequential scan, do not reach for CREATE INDEX yet. Check three things in order: is the filtered column wrapped in a function or a cast, does the index you expect lead with the column you filtered on, and is this a LIKE against a non-C-locale text column. Those three account for most of the slow queries I have looked at on systems that already had a reasonable schema.

Then, and only then, add the index, and go delete two unused ones while you are in there. Whether your data lives in Postgres or somewhere else, the storage engine you chose does not exempt you from this. Every system that makes reads fast by keeping a sorted structure on the side charges you for it on write, and every one of them has rules about which queries it will and will not match. Postgres just documents its rules better than most.

Go read one plan today. Pick your slowest endpoint, get the query it runs, and look at the scan node. Odds are good the index is already there.