BrevFeed
Sign in

44. Common performance killers: missing index, function-wrapped columns, implicit casts

📖 Reading · 8 min
💡 Every code box below is live — edit it and hit Run.

This lesson is a checklist of the highest-frequency, highest-impact mistakes that silently defeat an index that otherwise looks correctly built. Each one produces the same symptom — EXPLAIN shows a Seq Scan where you expected an Index Scan — with a different root cause.

Killer 1: no index exists at all

The obvious one, included for completeness: a column being filtered or joined on frequently, with no index, forces a sequential scan every time. The fix is simply creating the right index — everything in this module up to now has been about how to do that well. The remaining killers are subtler: cases where an index exists but the query, as written, can't use it.

Killer 2: wrapping the indexed column in a function

CREATE INDEX idx_customer_email ON customer (email);

-- Index is USELESS here -- the index stores raw email values, not
-- lowercased ones. Postgres would have to lowercase EVERY row's stored
-- value to compare, which defeats the whole point of a sorted structure.
EXPLAIN SELECT * FROM customer WHERE lower(email) = 'mary.smith@sakilacustomer.org';

The index is sorted by the raw column value — wrapping the column in lower(), upper(), date_trunc(), arithmetic, or any other function means the engine is now looking for a value that isn't what the index is sorted by, so it can't use the index's sort order to narrow the search at all.

Fix 1 — rewrite the query to leave the column bare, moving the transformation to the other side of the comparison:

-- Compare the raw column against an already-lowercased literal instead.
EXPLAIN SELECT * FROM customer WHERE email = lower('MARY.SMITH@sakilacustomer.org');

Fix 2 — a functional (expression) index, when the transformation is inherent to how the column is always queried (e.g. genuinely case-insensitive email lookups are a real, permanent requirement, not a one-off):

CREATE INDEX idx_customer_email_lower ON customer (lower(email));
EXPLAIN SELECT * FROM customer WHERE lower(email) = 'mary.smith@sakilacustomer.org';
-- NOW this uses the index -- it's built on the exact expression being queried.

A functional index is sorted by the result of the expression, not the raw column — so a query has to use that exact expression (or one the planner can prove is equivalent) to benefit.

Killer 3: implicit type casts

-- customer_id is `integer`. Comparing against a TEXT literal forces an
-- implicit cast -- and depending on which side gets cast, the index may
-- or may not remain usable.
EXPLAIN SELECT * FROM customer WHERE customer_id = '1';    -- usually fine, Postgres casts the literal
EXPLAIN SELECT * FROM rental WHERE customer_id::text = '1'; -- BAD -- explicitly casts the COLUMN, defeats the index

The second form is a special case of Killer 2 — customer_id::text is a function-like transformation of the column itself, so it has the exact same effect as wrapping it in lower(). Postgres is often smart enough to implicitly cast a literal to match the column's type without breaking index usage (customer_id = '1' typically gets silently rewritten to compare against the integer 1) — but casting the column explicitly always defeats the index, no exceptions. The practical rule: never cast the column side of a comparison if you want an index on that column to remain usable.

Killer 4: mismatched, missing, or stale statistics

The planner's row-count estimates (the rows= in EXPLAIN) come from table statistics, gathered by ANALYZE (run automatically by autovacuum under normal conditions). If those statistics are stale — a table that changed dramatically since the last ANALYZE — the planner can make a confidently wrong decision, choosing a Seq Scan when an Index Scan would actually be far cheaper, or vice versa. This is diagnosed exactly the way the EXPLAIN ANALYZE lesson flagged: a large gap between EXPLAIN ANALYZE's estimated and actual row counts is the tell. The direct fix is ANALYZE tablename to refresh statistics (per-column histograms and distinct-value estimates the planner consults).

Killer 5: OR conditions across different columns

CREATE INDEX idx_film_rating ON film (rating);
CREATE INDEX idx_film_rate ON film (rental_rate);

-- A single index can't efficiently serve an OR across TWO DIFFERENT
-- indexed columns -- Postgres CAN combine separate index scans with a
-- BitmapOr, but it's meaningfully more expensive than a single-column lookup.
EXPLAIN SELECT * FROM film WHERE rating = 'R' OR rental_rate > 4.00;

Postgres can handle this via a BitmapOr (scan both indexes separately, then union the results) — worth recognizing in a plan, not a sign something is broken — but it's real, additional work compared to a single index lookup, and often ends up costing enough that a plain sequential scan wins anyway on a smaller table. If this exact OR pattern is a frequent, performance-critical query, a composite or purpose-built index covering the actual access pattern is usually a better fix than relying on BitmapOr every time.

Check yourself

  1. Why does WHERE lower(email) = '...' defeat a plain index on email, even though the index exists and covers that exact column?
  2. Name two ways to fix the function-wrapped-column problem.
  3. Why does explicitly casting the column side of a comparison always defeat an index, while casting a literal usually doesn't?
← Previous Next →