Why a query is slow, and what an index does

Slow almost always means reading too much

A database with no help reads every row of the table and checks each one. Four million payment rows at 200 bytes each is about 800 MB of reading to answer a question whose answer is six rows. Even from memory that is not free. From disk it is seconds.

Ask the database what it is doing rather than guessing:

EXPLAIN ANALYZE
SELECT id FROM payments WHERE student_id = 4412;

-- Seq Scan on payments  (cost=0.00..84210.00 rows=1 width=8)
--   Filter: (student_id = 4412)
--   Rows Removed by Filter: 3999994
--   Planning Time: 0.1 ms
--   Execution Time: 612.4 ms

Seq Scan means it read the whole table. Rows Removed by Filter: 3999994 means it threw away almost all of it. That line is the confession.

What an index actually is

An index is a second structure — normally a B-tree — holding the values of one or more columns in sorted order, each with a pointer to where the full row lives. Because it is sorted, the database can binary search it. Four million rows becomes about 22 comparisons instead of four million.

CREATE INDEX ON payments (student_id);

-- Index Scan using payments_student_id_idx on payments
--   (actual time=0.021..0.033 rows=6 loops=1)
--   Execution Time: 0.08 ms

612 ms to 0.08 ms. That is the whole trick, and it is a big trick.

Indexes are not free. Every INSERT, UPDATE and DELETE must update every index on the table. They take disk space. A table with twelve indexes that is written constantly will be slow to write and its indexes will mostly go unused. Index the columns you filter and join on, not every column you have.

When the index sits there unused

You hid the raw value behind a function.

WHERE date(created_at) = '2026-03-14'   -- index on created_at is useless
WHERE lower(email) = 'ada@example.com'  -- index on email is useless

The index stores created_at, not date(created_at). To compare, the database must compute the function for every row first, which means reading every row. Rewrite as a range, or build an index on the expression itself:

WHERE created_at >= '2026-03-14' AND created_at < '2026-03-15'
CREATE INDEX ON users (lower(email));

A leading wildcard. LIKE '%kumar' cannot use a B-tree, because a sorted list does not help you find things by their ending. LIKE 'kumar%' can.

The filter matches most of the table. WHERE is_active = true when 95% of rows are active. Following the index and then fetching 95% of the rows one at a time is slower than reading the table straight through. When the planner ignores your index here, it is right and you are wrong.

The table is small. Scanning 500 rows takes microseconds. No index will beat that.

The wins that are usually bigger than an index

Return less. Add the missing filter. Add a LIMIT. A query that returns 400,000 rows to an application that displays 20 is slow because of the 399,980, not because of the plan.

**Stop using SELECT *** on wide tables. If one column holds a 40 KB JSON blob, selecting it for every row means moving hundreds of megabytes across the network to display a name and a date.

Do not run one query per row. An application that fetches 900 orders and then runs one query per order to get the customer has made 901 round trips. At 3 ms each that is nearly three seconds, and the database will report every individual query as fast. One join does it in 12 ms. This is the most common performance bug in real software and it never shows up as a slow query in the logs.

Reading a plan without being an expert

Find the node with the largest actual time. That is your problem, not the scary-looking one at the top. Then compare the estimated rows= against the actual rows=. If the planner expected 3 rows and got 900,000, it chose its whole strategy on a bad guess — usually stale statistics (run ANALYZE) or a filter it cannot reason about, like a function on a column.