On this page
  1. Step 1 — Find the slow query
  2. Step 2 — Look at the plan
  3. Step 3 — Add the index
  4. Step 4 — Cover multi-column cases
  5. Step 5 — Watch the write cost
  6. Where AI coding assistants get this wrong
  7. Checklist
  8. FAQ
    1. What is EXPLAIN?
    2. What is a composite index?
    3. Why didn’t my index speed up the query?
  9. Related topics
  10. Sources
tutorial

How to Add an Index to a Slow SQL Query

Diagnose a slow query with EXPLAIN, add the right index, and verify the speedup. A practical PostgreSQL tutorial.

Software Engineeringbeginner7 min readUpdated 2026-08-15

Quick answer

  • Find the slow query, run EXPLAIN to confirm a full table scan, add an index on the filtered column, then re-run EXPLAIN to confirm the speedup.
  • Add the index only after confirming the query would use it.
  • Verify, don’t assume: EXPLAIN ANALYZE shows the plan and the actual time.

Step 1 — Find the slow query

Identify the query that’s actually slow — the one your app runs constantly, or the one that times out. A slow query you run once a day matters far less than a fast query you run a thousand times a second.

Step 2 — Look at the plan

Run the query’s plan:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

How to verify it worked / read it: if the plan says Seq Scan on orders (Postgres), the database is reading the whole table. That’s the signal you need an index.

Step 3 — Add the index

Add an index on the column the query filters by:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

How to verify it worked: re-run EXPLAIN ANALYZE. The plan should now say Index Scan using idx_orders_customer_id, and the execution time should drop sharply — often from seconds to milliseconds on a large table.

Step 4 — Cover multi-column cases

If the query filters by two columns together, a composite index on both may help:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

The column order matters: put the equality-filtered column first, then the range or sort column.

Step 5 — Watch the write cost

Each index adds overhead to inserts and updates. Confirm the index is actually used (EXPLAIN), and drop it if the access pattern changes and it stops helping.

Where this bites vibecoders

The pattern is textbook: the AI assistant generates the schema with primary keys only, the app runs full scans, and everything is fine until the table grows. The discipline is to check EXPLAIN before adding an index — an index that isn’t used is pure write overhead, and guessing wastes the same time as the slow query itself.

Where AI coding assistants get this wrong

  • Proposing an index without first confirming the query plan needs one.
  • Adding single-column indexes where a composite index is required.
  • Indexing the wrong column order in composite indexes.
  • Forgetting that indexes cost writes, and over-indexing.

Checklist

  • Identify the hot query before optimizing.
  • Run EXPLAIN ANALYZE and confirm a full scan.
  • Add the index on the filtered column(s).
  • Re-run EXPLAIN and confirm an index scan and faster time.
  • Re-check the index is still used as queries evolve.

FAQ

What is EXPLAIN?

EXPLAIN (and EXPLAIN ANALYZE) shows the query plan the database will use — which indexes, which scans, in what order. ANALYZE actually executes the query and reports real timings. It’s the essential tool for knowing, rather than guessing, why a query is slow.

What is a composite index?

A composite index covers multiple columns in one index. It helps queries that filter or sort by those columns together, but the column order matters: leading columns are used first. See What Is Database Indexing?.

Why didn’t my index speed up the query?

Common reasons: the query can’t use the index (a function wraps the column, or a leading wildcard in a LIKE), the table is small enough that a scan is actually faster, or the index doesn’t match the query’s columns. EXPLAIN will show you which.

Sources

Tags:DatabasesIndexingSQL
Share: