Why Your Index Isn't Being Used (Even Though You Created It)

You Created the Index. The Planner Ignores It.

You've got a slow query. You look at the table, identify the column in the WHERE clause, and create an index. Problem solved, right?

You run EXPLAIN ANALYZE. The planner is still doing a sequential scan. Every single row. Your index sits there, unused, like a door nobody opens.

This is one of the most frustrating experiences in database performance work. You know the index should help. The planner disagrees. And the planner is almost never wrong -- it just knows something you don't.

How the Query Planner Decides

Before diving into specific failures, you need to understand how the planner thinks. It doesn't blindly use every available index. It makes a cost-based decision.

The planner considers:

1. How many rows will the condition match? (selectivity)
2. How expensive is the index lookup + heap fetch vs a straight sequential scan?
3. Are the table statistics current?

An index scan isn't free. For each matching entry in the index, the database must do a random I/O to fetch the actual row from the heap. Sequential scans do sequential I/O, which is much faster per byte. When the index would return a large fraction of the table, sequential scan wins.

The Six Index Killers

Explore each scenario below. Click EXPLAIN ANALYZE to see how the planner reacts and why the index gets ignored.

SELECT * FROM users
WHERE LOWER(email) = 'alice@example.com';

CREATE INDEX idx_email ON users(email);

1. Functions on Indexed Columns

This is the most common trap. You have an index on email, but your query uses LOWER(email) or TRIM(email) or EXTRACT(YEAR FROM created_at).

The index stores raw email values in a B-tree. When you wrap the column in a function, the planner can't binary-search the tree -- the transformed values aren't in the index.

-- Index on email -- but this query wraps it in LOWER()
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
 
-- Planner sees: "I have an index on email, but the query
-- asks about LOWER(email). Those are different values.
-- I can't use this index."

The fix: expression indexes

CREATE INDEX idx_email_lower ON users(LOWER(email));

Now the index stores the pre-computed LOWER(email) values. The planner recognizes that the query expression matches the index expression exactly.

2. Implicit Type Conversion

This one is subtle and infuriating. The column is an integer, but you pass a string in the WHERE clause. The database silently casts every row's value for comparison.

-- order_id is an integer column with an index
SELECT * FROM orders WHERE order_id = '12345';
 
-- What the database actually does:
SELECT * FROM orders WHERE CAST(order_id AS text) = '12345';
-- Now it's a function on the column -- same problem as #1

The fix: match your types

Always pass parameters that match the column type. In application code, ensure your ORM or query builder sends the right type. In PostgreSQL, you can use :: to be explicit:

SELECT * FROM orders WHERE order_id = 12345;
-- or explicitly: WHERE order_id = '12345'::integer

3. Wrong Composite Index Column Order

Composite (multi-column) indexes follow the leftmost prefix rule. The index on (created_at, status) can serve queries that filter on created_at, or on created_at AND status. But it cannot efficiently serve queries that filter only on status.

Think of it like a phone book sorted by last name, then first name. You can look up all "Smiths" easily. You can look up "Smith, John" easily. But finding all "Johns" regardless of last name? You'd have to scan every page.

-- Index: (created_at, status)
-- This CANNOT use the index efficiently:
SELECT * FROM orders WHERE status = 'shipped';
-- status is the SECOND column -- no leading prefix match
 
-- This CAN use the index:
SELECT * FROM orders WHERE created_at > '2026-01-01' AND status = 'shipped';

The fix: put high-selectivity filter columns first

-- Equality columns before range columns
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

The general rule: equality conditions first, range conditions last. This gives the planner the best chance of narrowing down results quickly.

4. Low Selectivity (The Boolean Column Trap)

You index a boolean column like is_active where 95% of rows are true. The planner does the math: using the index means 475,000 random I/O operations to fetch rows from the heap. A sequential scan reads the table in order. Sequential scan wins.

An index is most valuable when it narrows down the result to a small fraction of the table. When it returns most of the table, the overhead of the index makes things slower, not faster.

The fix: partial indexes

Index only the rows that matter. If 95% of users are active and you're looking for the 5% that aren't:

-- Instead of indexing the whole column:
CREATE INDEX idx_inactive_users ON users(is_active) WHERE is_active = false;

The partial index is tiny (5% of the data), always selective, and always used by the planner when the query matches the WHERE condition.

5. OR Across Different Columns

OR conditions across different columns put the planner in a tough spot. It can't use one index to satisfy both sides of the OR.

SELECT * FROM products
WHERE name ILIKE '%widget%' OR category_id = 5;

The ILIKE '%widget%' with a leading wildcard can't use a B-tree index at all (the search value could be anywhere in the string). And even if category_id has an index, the OR means the planner needs rows matching either condition -- it can't combine two index scans cleanly.

The fix: UNION for index-friendly OR

-- Each branch can use its own index
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE name ILIKE '%widget%';

For the ILIKE side, consider a pg_trgm GIN index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);

6. Stale Table Statistics

This is the sneakiest one. Your index is correct. Your query is correct. But the planner makes a bad decision because it has outdated information about your data.

The planner relies on statistics -- row counts, value distributions, most common values, null fractions. When you bulk-load data, these stats can become wildly inaccurate.

The fix: ANALYZE

ANALYZE events;  -- refresh statistics for this table
-- or for the whole database:
ANALYZE;

PostgreSQL's autovacuum daemon runs ANALYZE periodically, but it can lag behind during heavy write bursts. After any bulk operation, manually ANALYZE the affected tables.

Debugging Checklist

When your index isn't being used, work through this in order:

Key Takeaways

The planner is almost always right. When it ignores your index, it's making a cost-based decision with the information it has. Your job is to make sure that information is accurate and that the query is written in a form the index can serve.

Functions kill indexes. LOWER(email), YEAR(created_at), implicit casts -- any transformation on the indexed column makes the index invisible. Use expression indexes or fix the query.

Column order in composite indexes is not optional. The leftmost prefix rule determines what queries a multi-column index can serve. Get it wrong and the index exists for nothing.

High selectivity is what makes indexes worthwhile. If your condition matches most of the table, the planner is right to skip the index. Use partial indexes to index only the selective subset.

Statistics drive plan quality. Stale statistics after bulk operations are the most invisible cause of bad query plans. ANALYZE is cheap -- run it after big changes.