Why Is My Query Slow? A Practical Guide to Database Indexing

Almost every "the app is slow" problem I've debugged ended at the same place: a database query scanning far more rows than it needed to. On day one, with 200 rows in the table, everything feels instant. Six months later, with 2 million rows, the same page takes eight seconds and nobody changed a line of code.

The fix is usually an index. But adding indexes blindly can make things worse, so let's understand what they actually do.

What an index really is

Think of a thick textbook. If you want every page that mentions "normalization," you have two options:

Read all 600 pages from start to finish. Flip to the index at the back, find "normalization → pages 45, 112, 310," and go straight there.

A database without an index does option 1. It's called a sequential scan (or full table scan): it reads every row and checks whether it matches. An index is option 2: a separate, sorted structure (usually a B-tree) that maps values to row locations, so the database can jump directly to the rows it needs.

The trade-off: every index takes disk space and must be updated on every INSERT, UPDATE and DELETE. Indexes make reads faster and writes slightly slower. That's why you don't just index every column.

Step 1: Find the slow query first

Don't guess. Measure. In PostgreSQL, put EXPLAIN ANALYZE in front of your query:

sql EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 20;

Output (simplified):

Limit (actual time=812.4..812.5 rows=20) -> Sort (actual time=812.4..812.4 rows=20) -> Seq Scan on orders (actual time=0.03..801.9 rows=356 loops=1) Filter: (customer_id = 4821) Rows Removed by Filter: 1999644 Execution Time: 812.6 ms

Two things jump out: Seq Scan and Rows Removed by Filter: 1,999,644. The database read two million rows to return 356. That's your problem.

To find slow queries across the whole app, enable the pg_stat_statements extension and sort by total execution time. In MySQL, turn on the slow query log. If you use Django, django-debug-toolbar shows every query each page runs.

Step 2: Add the right index sql CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Run EXPLAIN ANALYZE again:

Limit (actual time=0.9..0.9 rows=20) -> Sort -> Bitmap Heap Scan on orders -> Bitmap Index Scan on idx_orders_customer_id Execution Time: 1.1 ms

From 812ms to about 1ms. That's the kind of improvement indexes give when they're in the right place.

On a busy production table, use CREATE INDEX CONCURRENTLY in PostgreSQL so you don't lock writes while the index builds.

Step 3: Understand composite indexes (column order matters)

Our query filters by customer_id and sorts by created_at. A single index on both columns handles the filter and the sort:

sql CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

Now the database walks the index in the already-sorted order and stops after 20 rows. No separate sort step at all.

The key rule is the leftmost prefix. A composite index on (customer_id, created_at) works like a phone book sorted by last name, then first name:

WHERE customer_id = 5 → uses the index ✅ WHERE customer_id = 5 AND created_at > '2026-01-01' → uses the index ✅ WHERE created_at > '2026-01-01' alone → usually can't use it efficiently ❌ (like searching a phone book by first name only)

A good rule of thumb: put columns you filter with = first, then columns you filter by range (>, <, BETWEEN) or sort by.

Which columns deserve an index?

Good candidates:

Foreign keys used in joins (customer_id, product_id). PostgreSQL does not index these automatically; Django's ORM does add them for ForeignKey fields, but raw SQL schemas often miss them. Columns in frequent WHERE clauses (status, email, slug). Columns you ORDER BY on large tables, especially with LIMIT. Columns that must be unique (UNIQUE constraints create an index automatically).

Poor candidates:

Tiny tables (a few hundred rows). A sequential scan is already fast. Columns with very few distinct values used alone, like a boolean is_active where 95% of rows are true. For these, a partial index is smarter: sql CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

This indexes only pending orders, so it stays small and fast.

Five mistakes that make indexes useless

1. Wrapping the column in a function. WHERE LOWER(email) = 'a@b.com' can't use a normal index on email. Either store emails lowercased or create an expression index: CREATE INDEX ON users (LOWER(email));

2. Leading wildcards. WHERE name LIKE '%phone%' can't use a B-tree index. LIKE 'phone%' can. For real text search, use PostgreSQL full-text search or the pg_trgm extension.

3. Type mismatches. Comparing a text column to a number (WHERE phone = 9800000000) forces a conversion on every row. Match your types.

4. Too many indexes. I've seen tables with 15 indexes where only 3 were ever used. Each one slows every write. In PostgreSQL, check pg_stat_user_indexes for indexes with idx_scan = 0 and consider dropping them.

5. The N+1 problem, which no index can fix. If your code runs one query to fetch 50 orders and then 50 more queries to fetch each customer, each query might be fast but 51 round trips are not. In Django, use select_related() for foreign keys and prefetch_related() for many-to-many or reverse relations:

python # 51 queries orders = Order.objects.all()[:50] for o in orders: print(o.customer.name)

# 1 query orders = Order.objects.select_related("customer")[:50] A simple workflow to follow Find the slowest queries (pg_stat_statements, slow query log, or debug toolbar). Run EXPLAIN ANALYZE and look for sequential scans on big tables and large "Rows Removed by Filter" numbers. Add one targeted index, matching the WHERE and ORDER BY columns in the right order. Run EXPLAIN ANALYZE again and confirm the plan changed. Periodically remove indexes nobody uses. The takeaway

Indexes aren't advanced database magic. They're a sorted shortcut that lets the database skip work. Measure first, index the columns your queries actually filter and sort by, respect column order in composite indexes, and don't forget that N+1 queries are a code problem, not an index problem.

Get this right and you'll fix a surprising number of "the site is slow" complaints without touching your frontend at all.