Why is my SQL query slow even though the column is indexed?

The column is indexed. The query is still slow. The EXPLAIN output says Seq Scan, twelve million rows, forty seconds. One wrapped function is usually responsible:

-- created_at is indexed
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;

Why the index sat idle

An index is a sorted copy of a column with pointers back to the rows. Sorted is the whole trick: the engine can binary-search its way to the value in a handful of hops instead of walking the table.

YEAR(created_at) is not that column. It is a brand-new value computed from it, and nothing stores those computed values in sorted order. So the engine has no choice: compute the function for every row, twelve million times.

The sargable rewrite

Ask for a range of raw values instead of a computed one:

SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

Same rows, same meaning, but the predicate now compares the indexed column directly, so the engine jumps straight to the start of the range and reads until the end. This is what "sargable" means: written so the search argument can use the index. The pattern generalizes to prefixes (LIKE 'abc%' is sargable, LIKE '%abc' is not) and to any range.

What an index actually is

CREATE INDEX idx_orders_created
ON orders (created_at);

Think book index: a sorted list of terms, each pointing at a page. The table itself stays unsorted; the index is a side structure (almost always a B-tree) that the engine keeps in sync for you. Lookups cost a few tree hops instead of a full walk, which is the difference between 0.2 seconds and 40.

Trap 1: the leftmost prefix

A composite index only works from its first column:

CREATE INDEX idx ON orders
  (customer_id, created_at);

WHERE customer_id = 42            -- uses idx
WHERE created_at > '2026-01-01'   -- ignores idx

The index is sorted by customer_id first and by created_at only within each customer. Like a phone book sorted by last name: perfect for finding a Dupont, useless for finding every Marie. Order composite columns by how your queries actually filter.

Trap 2: indexes are not free

Every index is a promise the engine has to keep on every write. Each INSERT and UPDATE maintains every index on the table, so a table with eight speculative indexes pays eight times on every load. Low-cardinality columns (a 3-value status flag) barely benefit. And SELECT * on wide rows still has to visit the table even after the index finds the matches.

The discipline: index the columns your WHERE and JOIN clauses actually use, check with EXPLAIN that the plan says Index Scan and not Seq Scan, and drop the indexes no query ever touches.

The takeaway

An index is a sorted copy the engine can seek in. Keep your predicates sargable so the sort applies, respect the leftmost prefix on composite indexes, and treat every index as a write cost you must justify. The print-ready PDF above fits the whole checklist on one page.