Why Does Your Query Take 8 Seconds? Getting to 8 Milliseconds With a Database Index

In the first month after launch, everything was lightning fast. The order list opened instantly; search returned results instantly. A year later the same page takes 8 seconds, customers complain and the server CPU sits at 100%. The code didn't change. Only the data did: 5,000 orders became 5 million.

The ending of this story is often a surprisingly simple line: CREATE INDEX. In this post we explain how indexes work, how to choose the right one and how to diagnose why a query is slow, with PostgreSQL examples. Most of it applies to MySQL, SQL Server and other relational databases too.

In short:

• Without an index, the database reads every row in the table to find what it wants (a sequential scan). On 5 million rows that takes seconds.
• An index works like the index at the back of a book: the database jumps straight to the relevant rows.
• EXPLAIN ANALYZE shows how a query runs and where the time goes. Don't guess; measure.
• Indexes aren't free: each one slows down writes and takes disk space. Indexing every column isn't the answer.

What is an index? The phone book analogy

You're looking up "Smith, John" in a thick phone book. Since it's sorted alphabetically by surname, you go straight to "S" and find it in seconds.

Now, in the same book, look up the person whose number is 555-0142. The book isn't sorted by number. Your only option is to start on page one and check every line.

A database table is like that second, unsorted case. When you add an index on a column, the database keeps that column's values separately in a sorted structure and records where each value lives in the table. On a search, it checks that sorted structure first, then goes straight to the matching rows.

PostgreSQL's default index type is the B-tree, a balanced tree structure: even in a table with millions of rows, the value is reached in a few steps. When the table grows tenfold, lookup time grows only slightly, not tenfold.

Diagnosis: EXPLAIN ANALYZE

The first step to speeding up a slow query is seeing why it's slow. In PostgreSQL, put EXPLAIN ANALYZE in front of a query and the database runs it and reports the path it took.

Example: we look up one customer's orders in a 5-million-row orders table.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 48213;

Without an index, the output looks something like this (timings are examples and vary by hardware):

Seq Scan on orders  (cost=0.00..104186.00 rows=52 width=64)
                    (actual time=12.4..812.6 rows=47 loops=1)
  Filter: (customer_id = 48213)
  Rows Removed by Filter: 4999953
Execution Time: 812.9 ms

Two critical clues: Seq Scan (the whole table was read from start to finish) and Rows Removed by Filter: 4999953 (about 5 million rows were read and thrown away to find 47). That's the signature of a missing index.

Let's add the index:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

The new plan for the same query:

Index Scan using idx_orders_customer_id on orders
                    (actual time=0.03..0.21 rows=47 loops=1)
  Index Cond: (customer_id = 48213)
Execution Time: 0.25 ms

From 812 milliseconds to 0.25. Roughly 3,000 times faster without changing a line of code.

Which columns should you index?

Good candidates:

• Columns often used in WHERE conditions (customer_id, status, email),
• Foreign keys used in JOINs. PostgreSQL automatically indexes primary keys but not foreign key columns. That's the most commonly missed gap.
• Columns used in ORDER BY, especially with LIMIT ("the last 20 orders").

Weak candidates:

• Columns with very few distinct values, on their own (e.g. a column that's only "active/inactive"). An index that returns half the rows isn't much faster than reading the table.
• Very small tables. Reading a few hundred rows in order is already fast.

Composite indexes: order is everything

If your query filters on several columns, you can use a composite (multi-column) index:

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at);

This index is like a phone book sorted "first by surname, then by first name". So:

The reason for the last row is simple: if you search the phone book by first name only, the surname ordering doesn't help. The rule: put the column filtered by equality first, and the column filtered by range or used for sorting last.

Common mistakes that defeat an index

Index there but not used? The most common reasons:

1. Wrapping the column in a function. Even with an index on email, this query can't use it:

SELECT * FROM users WHERE lower(email) = 'jane@example.com';

The index is sorted by email values, not by lower(email). The fix is an expression index:

CREATE INDEX idx_users_email_lower ON users (lower(email));

The same problem shows up with dates: instead of WHERE date(created_at) = '2026-09-30', write WHERE created_at >= '2026-09-30' AND created_at < '2026-10-01'.

2. LIKE with a leading wildcard. WHERE name LIKE 'Joh%' can use a B-tree index (with suitable settings), but WHERE name LIKE '%son' can't. It's like searching the phone book for "surnames ending in -son". For searching within text you need PostgreSQL's full-text search or trigram indexes via the pg_trgm extension.

3. Type mismatches. Comparing a numeric column with text, or vice versa, can prevent index use in some cases. Make sure parameter types match the column type.

4. Stale statistics. The database decides whether to use an index based on statistics about the table. Running ANALYZE orders; after a big data load helps the planner decide well. PostgreSQL also does this automatically via autovacuum.

The cost of indexes

"If it speeds things up this much, let's index every column" is tempting but wrong:

• Write cost: every INSERT, UPDATE and DELETE must update all related indexes too. A table with ten indexes gets noticeably slower to write.
• Disk and memory: indexes take space. For performance, it matters that frequently used indexes fit in memory.
• Unused indexes: indexes added over time that no query uses only add load. In PostgreSQL, the idx_scan column of the pg_stat_user_indexes view shows how many times an index has been used.

Adding an index on a live system

Adding an index to a big table with a plain CREATE INDEX blocks writes to the table for the duration. On a live e-commerce site that could mean minutes without taking orders. PostgreSQL's solution:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY takes longer but doesn't block writes. Note that it can't run inside a transaction block, and if it fails it can leave an invalid index behind; in that case drop and recreate it.

Which queries are slow? Find them first

Find which queries need an index with data, not guesswork. PostgreSQL's pg_stat_statements extension lists the queries that consume the most total time:

SELECT query, calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Often the top of the list is a query that isn't very slow on its own but runs hundreds of times a second. That's where the real win is.

Frequently asked questions

Do I need to add an index on the primary key?

No. PostgreSQL automatically creates indexes for primary keys and UNIQUE constraints. It doesn't for foreign key columns, though; add those yourself.

I use an ORM. Do indexes still concern me?

Yes. ORMs (Hibernate, Entity Framework, Prisma, etc.) write SQL for you but usually expect you to define indexes. Looking at the queries your ORM generates in the logs and inspecting them with EXPLAIN ANALYZE is a good habit.

Why does the database still do a Seq Scan after I added an index?

The planner may have decided the index would be more expensive for this query. If the query returns a large share of the table, reading it in order really is faster. Or one of the mistakes above (a function wrapper, a type mismatch) is keeping the index from being used.

My database is slow. Should I buy a bigger server?

Check indexes and queries first. Trying to fix a missing-index slowdown with hardware is both expensive and temporary: as data keeps growing, the problem comes back.

The right index can be the difference between an app that slows down as it grows and one that keeps its speed. If you want to diagnose your app's performance problems or build scalable infrastructure, reach us through our web application development page.

Sources