When an index can't help

Part of the free LogicWiz SQL & Databases course, Module 14: Indexes and query plans. The queries and practice run in the app on real Postgres.

Meher indexed email, yet the login query still does a Seq Scan. Kabir indexed status, and the delivered-orders report ignores it. Both indexes are fine. The queries are the problem.

You'll learn to

Spot conditions that hide a column from its index: functions, casts, maths, a leading %
Rewrite a date filter as a range the index can use
Explain why low-selectivity filters skip the index, and what indexes cost writes
Build expression indexes and partial indexes

Learn

The index stores the column, not f(column)

An index on email is a sorted list of email values. WHERE lower(email) = '[email protected]' asks about lower(email), a value the index doesn't contain and isn't sorted by. So Postgres must compute lower() for every row: Seq Scan.

The same happens with any wrapper around the column: EXTRACT(MONTH FROM ordered_on), ordered_on::text, amount * 1.18, customer_id + 0.

A condition the index can use is called sargable (Search ARGument ABLE). Rule: leave the indexed column alone on one side, and do the work on the other side.

A function on the column

EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE email = '[email protected]';
EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE lower(email) = '[email protected]';

Plain email = … uses idx_customers_email. lower(email) = … is a Seq Scan, even though the index is right there.

Rewrite a date function as a range

EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE EXTRACT(MONTH FROM ordered_on) = 3;
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE ordered_on >= DATE '2025-03-01'
  AND ordered_on <  DATE '2025-04-01';

Same orders (all of 2025's March), different plans. The range leaves ordered_on bare, so the B+ tree can jump to 1 March and walk to 31 March. Use < next month rather than <= 31st; it also works for timestamps.

LIKE: a prefix works, a leading % doesn't

EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE email LIKE 'Riya42%';
EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE email LIKE '%[email protected]';

LIKE 'Riya42%' becomes a range (>= 'Riya42' and < 'Riya43'), like finding words starting with "Riy" in a dictionary. A leading % could match anywhere, so Seq Scan. (This browser database uses the simple C collation; on servers with a language collation, a prefix LIKE needs an index declared with text_pattern_ops. Searching inside text needs a trigram or full-text index.)

Low selectivity: the index is skipped on purpose

EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE status = 'delivered';
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE status = 'pending';

delivered is 97% of orders: reading the table straight through is cheaper than 29,100 index jumps, so Postgres chooses a Seq Scan. pending is 1%: the index wins. The planner decides from the statistics, per value.

Indexes cost writes

Every index is a second copy of some data that must stay in step with the table:

INSERT adds an entry to every index on the table.
UPDATE of an indexed column (and often any update, because MVCC writes a new row version) touches the indexes.
DELETE leaves index entries for VACUUM to clean up.
Each index takes disk space and memory.

A table with 12 indexes makes every UPI payment insert do 13 writes. So index what your frequent queries filter, join and sort on, and drop indexes no query uses (pg_stat_user_indexes shows how often each is scanned).

Expression and partial indexes

-- index the expression the query uses
CREATE INDEX idx_customers_email_lower ON customers (lower(email));
EXPLAIN (COSTS OFF)
SELECT * FROM customers WHERE lower(email) = '[email protected]';
-- index only the rows the query wants
CREATE INDEX idx_orders_pending ON orders (ordered_on) WHERE status = 'pending';
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE status = 'pending' AND ordered_on = DATE '2025-03-02';

An expression index stores lower(email), so the case-insensitive login can use it. A partial index stores only the 300 pending orders: tiny, cheap to maintain, and perfect for the "pending orders" dashboard. A query must repeat the index's WHERE condition (status = 'pending') to use it.

Watch for hidden wrappers: WHERE DATE(created_at) = …, WHERE amount * 1.18 > 1000 (write amount > 1000 / 1.18), WHERE phone::bigint = 9800000042, WHERE COALESCE(city, '') = 'Pune'. Each one turns an indexed column into an expression. MySQL adds one more: comparing a VARCHAR column to a number (WHERE phone = 9800000042) converts every row and skips the index; Postgres raises an error instead.

Interview favourite: "I added an index and the query is still slow. Why?" Answer with the list: a function, cast or maths on the column; a leading wildcard; a condition matching most rows; the column is not the leftmost in a composite index; stale statistics (run ANALYZE); an OR across different columns; or the table is so small that a Seq Scan is genuinely cheaper.

In one line

Keep the indexed column bare (no functions, casts, maths or leading %); low-selectivity filters skip indexes; every index slows writes; use expression and partial indexes for the queries you really run.

Interview corner

I added an index but the query still does a full scan. Why?

Common reasons: the WHERE wraps the column in a function or cast (lower(email), date(created_at)); a LIKE starts with %; the condition matches a large share of rows so a scan is cheaper; the column isn't the leading column of a composite index; statistics are stale; or the table is small. EXPLAIN shows which.

What are partial and expression indexes?

A partial index covers only rows matching a WHERE (… WHERE status = 'pending'), so it is small and cheap and serves queries with that same condition. An expression index stores a computed value (lower(email)), so queries filtering on that expression can use it.

Why not index every column?

Each index slows every INSERT, many UPDATEs and DELETEs, takes disk and memory, and the planner won't use most of them. Index what frequent queries filter, join and sort on, prefer composite indexes that serve several queries, and drop unused ones.