Read the query plan before you add the index: EXPLAIN ANALYZE, Seq Scan vs Index Scan

Your query is slow. You know the fix: add an index. You pick the column that feels like the one — the one in the WHERE clause you stare at most — run CREATE INDEX, and the query is exactly as slow as before. So you add another index. And another. Now you have six indexes, every write is slower, and the read you were chasing never moved.

The database has been willing to tell you which column to index this whole time. You have to ask it with EXPLAIN ANALYZE and read three lines of the answer.

This is the difference between indexing by vibes and indexing by evidence. Everything below is real output captured from PostgreSQL 16.14 against a 500,000-row table. The commands are copy-pasteable; the two footguns at the end are the ones that make people declare “indexes don’t work” and go back to guessing.

Set up a table worth measuring

Indexes only matter at scale, so we need real rows. Half a million users, one known needle to search for:

$ createdb qdemo
$ psql qdemo
CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  region     text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (email, region)
SELECT 'user' || g || '@example.com',
       (ARRAY['us','eu','apac','sa'])[1 + (g % 4)]
FROM generate_series(1, 500000) AS g;

ANALYZE users;

That ANALYZE at the end is not optional — it refreshes the statistics the planner reads to make its decisions. Skip it and the planner is guessing about a table it hasn’t looked at.

A note on the plans below: I ran SET max_parallel_workers_per_gather = 0 first, so the plans read as a single clean node instead of a Gather over parallel workers. With parallelism on, the first plan below shows up as a Parallel Seq Scan under a Gather — same lesson, more boxes. Turning it off is a readability choice, not a fix.

Step 1: ask the database what it’s doing

EXPLAIN shows the plan the planner chose. EXPLAIN ANALYZE actually runs the query and shows what happened — estimated vs. actual rows, and the real clock time. Reach for ANALYZE when you’re diagnosing; reach for plain EXPLAIN when you only want the estimate without paying to run the query (more on that footgun later).

Here is our lookup with no index yet:

qdemo=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user398765@example.com';
                                    QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..10916.00 rows=1 width=41)
                    (actual time=26.504..33.245 rows=1 loops=1)
   Filter: (email = 'user398765@example.com'::text)
   Rows Removed by Filter: 499999
 Planning Time: 0.221 ms
 Execution Time: 33.279 ms

Read three things and you’ve read the plan:

• Seq Scan — the database walked the entire table, top to bottom. That’s the tell. On a big table you never want to see this for a single-row lookup.
• Rows Removed by Filter: 499999 — it looked at all 500,000 rows and threw away all but one. That’s 499,999 rows of wasted work to find your needle.
• Execution Time: 33.279 ms — remember this number. It’s the baseline we’re about to beat.

You’ll know you’re reading it right when you can point at the Seq Scan line and say “it checked every row.” That single fact is why the query is slow.

Step 2: add the index the plan asked for

The WHERE clause filters on email, and the plan proved email is the column doing the throwing-away. So that’s the column to index — not the one that felt important, the one the plan named.

qdemo=# CREATE INDEX idx_users_email ON users (email);
CREATE INDEX

qdemo=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user398765@example.com';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=41)
                                            (actual time=0.032..0.033 rows=1 loops=1)
   Index Cond: (email = 'user398765@example.com'::text)
 Planning Time: 0.281 ms
 Execution Time: 0.064 ms

Seq Scan became Index Scan using idx_users_email. The Rows Removed by Filter line is gone entirely — instead of filtering 500,000 rows, the index jumped straight to the one. And the number that matters: 33.279 ms → 0.064 ms, about 500× faster, on the same query and the same data.

You’ll know it worked when the plan says Index Scan (or Bitmap Index Scan) with an Index Cond, and the execution time drops off a cliff. If it still says Seq Scan after you built the index, don’t add a second index — read the next two sections, because you probably hit one of these.

Footgun 1: a composite index only seeks on its leading column

Say you’ve got an orders table at the same half-million-row scale, with a query that filters two ways. So you build one index covering both columns:

CREATE INDEX idx_orders_status_cust ON orders (status, customer_id);

Then you run the query that filters on customer_id — a selective lookup, 10 rows out of 500,000 — and it crawls:

qdemo=# EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using idx_orders_status_cust on orders  (cost=0.42..6373.24 rows=10 width=23)
                                                    (actual time=0.399..5.620 rows=10 loops=1)
   Index Cond: (customer_id = 12345)
 Planning Time: 0.307 ms
 Execution Time: 5.650 ms

It did use the index — but look at the cost (6373) and the time (5.6 ms). It read the entire index to find customer_id, because a B-tree on (status, customer_id) is sorted by status first. With no filter on status, customer_id is scattered all through the index and can’t be seeked to. The composite index degraded from a seek into a full scan.

Flip the column order so the column you actually filter on comes first, and the same query becomes a real seek:

qdemo=# CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);
qdemo=# EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;
                                          QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.50..42.98 rows=10 width=23)
                             (actual time=0.061..0.111 rows=10 loops=1)
   Recheck Cond: (customer_id = 12345)
   ->  Bitmap Index Scan on idx_orders_cust_status  (cost=0.00..4.50 rows=10 width=0)
                                                    (actual time=0.051..0.051 rows=10 loops=1)
         Index Cond: (customer_id = 12345)
 Planning Time: 0.279 ms
 Execution Time: 0.156 ms

Same query, same rows, only the index’s column order changed: 5.650 ms → 0.156 ms. The rule: a composite index (a, b) is a fast seek for filters on a, or on a and b — but not for a filter on b alone. Put the column you search by first, or give it its own index.

Footgun 2: wrap the column in a function and the index disappears

You built idx_users_email in Step 2 and it’s perfect. Then, somewhere in the codebase, the query does a case-insensitive compare:

qdemo=# EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'user398765@example.com';
                                    QUERY PLAN
-------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..12166.00 rows=2500 width=41)
                    (actual time=121.799..152.854 rows=1 loops=1)
   Filter: (lower(email) = 'user398765@example.com'::text)
   Rows Removed by Filter: 499999
 Planning Time: 0.246 ms
 Execution Time: 152.894 ms

Back to a Seq Scan, and slower than the un-indexed original — because now it computes lower() on all 500,000 rows before comparing. The index is on email, but you’re not searching email, you’re searching lower(email), and to the planner that is a different thing it has no index for.

The fix is an expression index on the exact expression you filter by:

qdemo=# CREATE INDEX idx_users_lower_email ON users (lower(email));
qdemo=# EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'user398765@example.com';
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=71.80..4151.30 rows=2500 width=41)
                            (actual time=0.029..0.030 rows=1 loops=1)
   Recheck Cond: (lower(email) = 'user398765@example.com'::text)
   ->  Bitmap Index Scan on idx_users_lower_email  (cost=0.00..71.17 rows=2500 width=0)
                                                   (actual time=0.026..0.026 rows=1 loops=1)
         Index Cond: (lower(email) = 'user398765@example.com'::text)

152.894 ms → 0.056 ms. The index has to match the shape of the predicate. WHERE lower(email) = … needs an index on lower(email); WHERE email = … needs one on email. Same trap fires for WHERE date(created_at) = …, WHERE email || region = …, and any other function or math wrapped around the column.

This one, and the whole idea of measuring before you tune, came from the sister site’s Query Optimization quest — they cover the tuning theory straight; we cover the part where you add three indexes that do nothing first.

When this goes wrong

• You built the index and the plan still says Seq Scan — but the table is small. On a tiny table a sequential scan is genuinely faster than an index lookup, and the planner knows it. A 10-row table returns Seq Scan on tiny … Rows Removed by Filter: 9 no matter how many indexes you add, and that is correct — reading 10 rows beats the overhead of an index hop. Don’t fight it. Test on data the size of production.
• EXPLAIN looked instant but EXPLAIN ANALYZE took 20 seconds. Plain EXPLAIN only estimates the plan; EXPLAIN ANALYZE runs the query. On a slow or UPDATE/DELETE statement that matters — wrap it in BEGIN; … ROLLBACK; if you don’t want the side effects, and never EXPLAIN ANALYZE a DELETE you can’t undo.
• The index exists but the query returns 25% of the table. Indexes win when a filter is selective — a handful of rows out of many. A predicate like status = 'refunded' that matches a quarter of the table will correctly Seq Scan, because visiting that many scattered rows through an index is slower than one linear pass. An index is not a fix for “this query returns most of the table.”
• Every index you add makes writes slower. An index is a second structure the database maintains on every INSERT, UPDATE, and DELETE. Six indexes to chase one slow read is how you trade a read problem for a write problem. Add the index the plan asked for, confirm the Seq Scan became an Index Scan, then stop.
• You read cost= and thought it was milliseconds. It isn’t. cost is the planner’s own unit-less estimate for comparing plans; actual time= in EXPLAIN ANALYZE is the real clock. Tune against actual time and Execution Time, not cost.

The whole loop is three steps: EXPLAIN ANALYZE the slow query, find the Seq Scan with a big Rows Removed by Filter, index the column the Filter line names — then re-run and confirm the number dropped. If it didn’t, the plan will tell you why, in the same three lines. You never have to guess which column; you were only ever guessing because you didn’t ask.

All query plans above are real output captured from PostgreSQL 16.14, reformatted only by wrapping the long (cost=…) (actual time=…) lines so they fit the page. The example addresses (user398765@example.com) are generated, not real.