PostgreSQL Is Lying to You About Your Indexes

I spent three hours once convinced I had a bug in my ORM. The query was slow. I had an index on the column. I could see the index in pgAdmin. And yet, full sequential scan, every single time.

Turns out I was the bug.

This is a collection of all the ways PostgreSQL will quietly ignore the index you carefully created, and why. No fluff. Just the stuff that actually trips people up.

First: EXPLAIN vs EXPLAIN ANALYZE

Before anything else, you need to stop using plain EXPLAIN and start using EXPLAIN ANALYZE.

-- this lies to you
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- this tells the truth
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Plain EXPLAIN shows the plan Postgres thinks it will use, based on statistics. EXPLAIN ANALYZE actually runs the query and shows what really happened, including actual row counts vs estimated row counts.

The gap between those two numbers is where most performance bugs live.

If the estimated rows say 1 and the actual rows say 84,000, your statistics are stale. Run ANALYZE orders; and check again.

The Small Table Problem

Postgres isn't stupid. If your table has 400 rows, a sequential scan is almost always faster than an index scan, and Postgres knows this. It reads the entire table in maybe 2-3 I/O operations. An index lookup would require reading the index pages and then the table pages. More work, not less.

So if you're testing on a table with dummy data and wondering why your index isn't being used, that's why. Fill the table with at least 10,000 rows and test again.

This is probably the most common "my index isn't working" complaint and the answer is almost always "your table is tiny."

LIKE '%foo' Will Never Use a B-tree Index

This one surprises people every time.

-- uses the index on email ✓
SELECT * FROM users WHERE email LIKE 'john%';

-- does NOT use the index ✗
SELECT * FROM users WHERE email LIKE '%john%';
SELECT * FROM users WHERE email LIKE '%john';

A B-tree index works like a phone book. You can look up everyone whose name starts with "Sm" because the book is sorted. But if you want everyone whose name contains "mi" anywhere? You have to read every single entry. The index structure gives you nothing.

For LIKE '%foo%' patterns, you need either:

-- pg_trgm extension + GIN index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING GIN (email gin_trgm_ops);

Or rethink whether you actually need fuzzy search, and if so, whether pg_trgm or a proper full-text search setup is the right answer for your scale.

The NULL Trap in Composite Indexes

This one is subtle and I've seen it bite senior engineers.

Say you have:

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

And you run:

SELECT * FROM orders WHERE user_id = 42 AND status IS NULL;

Postgres can use this index. But:

SELECT * FROM orders WHERE user_id = 42;

Also works, leading column is all you need.

The trap is the other direction. This cannot use the index:

SELECT * FROM orders WHERE status = 'pending';

Composite indexes are left-anchored. You must include the leftmost column(s) in your WHERE clause for the index to kick in. Skipping user_id and filtering on status alone means a full scan.

Also worth knowing: B-tree indexes in Postgres do index NULL values (unlike some other databases). So WHERE column IS NULL can use an index. A lot of people assume NULLs are invisible to indexes, they're not, in Postgres.

Expression Indexes (The Underused One)

What if you need to query on a lowercase version of an email?

-- this cannot use a plain index on email
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

Plain index on email is useless here because Postgres would have to call LOWER() on every row to compare. The index stores the raw values, not the computed results.

The fix:

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

Now Postgres stores lower(email) in the index and your query hits it directly. Same idea works for DATE_TRUNC, EXTRACT, JSON field access, anything.

The query in your application must match the expression exactly for this to work. LOWER(email) in the index and lower(email) in the query, Postgres is case-insensitive about function names here, but the expression structure must match.

Partial Indexes (The Underused One, Part Two)

A partial index only indexes rows that match a condition. Smaller index, faster writes, faster reads for that specific case.

Example: your app queries for unprocessed jobs constantly. The processed = false rows are maybe 0.1% of the table.

CREATE INDEX idx_jobs_unprocessed ON jobs (created_at)
WHERE processed = false;

This index is tiny. It only contains the unprocessed rows. Queries that filter on WHERE processed = false hit this index and it's absurdly fast.

Your query needs to include the condition from the index for Postgres to use it:

-- uses the partial index ✓
SELECT * FROM jobs WHERE processed = false ORDER BY created_at;

-- does NOT use it ✗
SELECT * FROM jobs ORDER BY created_at;

I've seen cases where a partial index cut query time from 800ms to 2ms. Not because the index algorithm is magic, because the index is 1/1000th the size of a full index.

When Postgres Ignores a Good Index Anyway

Sometimes the index is right, the query matches, the table is big enough, and Postgres still picks a seq scan.

Check random_page_cost. The default is 4.0, which assumes spinning disk. On SSDs (which is basically everything now, including most cloud DB instances), it should be closer to 1.1.

SET random_page_cost = 1.1;

Or set it per-session to test, then permanently in postgresql.conf or via your cloud provider's parameter group. With the wrong random_page_cost, Postgres's cost model overestimates how expensive random reads are and avoids index scans it should be using.

This one single setting has fixed "why isn't my index being used" complaints more times than I can count.

The Checklist

Before you blame Postgres:

• Run EXPLAIN (ANALYZE, BUFFERS), not plain EXPLAIN
• Table has enough rows (10k+ for meaningful index benchmarks)
• Pattern doesn't start with % (use pg_trgm for that)
• Composite index is being queried left-to-right
• Query matches the expression in an expression index exactly
• random_page_cost is set correctly for your storage type
• Statistics aren't stale, run ANALYZE tablename if in doubt

The index is almost never broken. The query is almost never broken. It's usually one of these seven things.