You added the index. You waited for the CREATE INDEX to finish. You re-ran the query. And EXPLAIN still shows a Seq Scan. The query is still slow. If that sounds familiar, you are not alone — it is one of the most common performance questions Houston DBAs bring up in tuning sessions.

Before you drop a second index on the same column, stop. The planner is not being stubborn. It is telling you something. Here are the six most common reasons PostgreSQL ignores your index, and how to fix each one.

Read the plan before you blame the index

Everything below starts with one habit: look at the actual plan, not the query. A quick EXPLAIN (ANALYZE, BUFFERS) tells you whether the planner chose a sequential scan, an index scan, and why. The AWS database team published a useful refresher on improving query performance with EXPLAIN plans — the same principles apply to any PostgreSQL deployment, including RDS, Aurora, and self-managed clusters.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE customer_id = 4521;
-- Seq Scan on orders  (cost=0.00..18432.00 rows=98 width=42)
--   Filter: (customer_id = 4521)

That plan means the planner looked at your index and decided a full scan was cheaper. Here is why.

1. You wrapped the column in a function

A btree index stores the raw column value. If your query runs WHERE lower(email) = '[email protected]', the planner must evaluate lower() on every row before it can compare — a plain index on email cannot be used. The fix is an expression index:

CREATE INDEX idx_orders_lower_email
ON orders (lower(email));

The same trap appears with date_trunc(), EXTRACT(), and arithmetic like WHERE price * 1.08 > 100. Index the expression, not the column.

2. Your LIKE pattern starts with a wildcard

WHERE name LIKE '%part%' cannot use a normal btree index because the leading wildcard means the value could start anywhere. If you genuinely need substring search, use pg_trgm and a GIN index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_orders_name_trgm
ON orders USING gin (name gin_trgm_ops);

If your pattern is 'part%' (prefix match), a regular btree index works fine — only the leading wildcard breaks it.

3. The column type does not match the query

If the column is varchar and you pass an integer, or the column is text and your parameter is varchar, PostgreSQL may add an implicit cast that defeats the index. This bites hard in ETL pipelines that load from CSV or JSON where types drift. Check the plan for a Filter with a cast, then align the types — or cast explicitly in the query:

-- Instead of relying on implicit cast:
SELECT * FROM orders WHERE external_id = '998877'::bigint;

4. The column is not selective enough

Indexes are not free. If a column has only a few distinct values — say, a status column where 90% of rows are 'active' — the planner correctly decides that scanning the table is cheaper than bouncing through the index. EXPLAIN will show a Seq Scan and that is the right answer.

If you still need fast lookups on that low-cardinality column, a partial index on the hot subset can help:

CREATE INDEX idx_orders_active_created
ON orders (created_at)
WHERE status = 'active';

5. Statistics are stale

The planner guesses row counts from pg_statistic, collected by ANALYZE. After a big INSERT, UPDATE, or DELETE, those estimates can be wildly off — and a planner that thinks a table has 1,000 rows will not use an index even when the table now has 10 million. Fix it:

ANALYZE orders;

If autovacuum is not keeping up on a busy table, raise the autovacuum threshold or schedule ANALYZE after your nightly batch jobs. Stale stats are the quietest performance killer in PostgreSQL.

6. The index does not match the query shape

A composite index (a, b) can serve queries filtering on a alone, but not b alone — the leading column must match. If your query filters on customer_id and sorts by created_at, an index on (customer_id, created_at) covers both. Also check that you are not selecting a dozen columns and forcing the planner to do a Heap Fetches for every row; an INCLUDE clause can make covering indexes more useful:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at)
INCLUDE (status, total);

The 60-second checklist

Next time PostgreSQL ignores your index, run through this list in order:

1. Read EXPLAIN (ANALYZE, BUFFERS) — no guessing.
2. Is the column wrapped in a function? Build an expression index.
3. Leading wildcard in LIKE? Use pg_trgm GIN.
4. Type mismatch? Align the types.
5. Low selectivity? Accept the seq scan or use a partial index.
6. Stale stats? ANALYZE and check autovacuum.
7. Wrong column order in a composite index? Reorder or add INCLUDE.

For Houston teams running energy, logistics, and healthcare workloads, slow queries rarely come from one dramatic mistake — they come from small planner misunderstandings like these. Fix the six causes above, and most “my index is broken” tickets disappear. The PostgreSQL documentation on indexes and EXPLAIN is the definitive reference when you want to go deeper.