---
title: "PostgreSQL Is Lying to You About Your Indexes"
description: "You added the index. The query is still slow. Here's why Postgres doesn't care about your feelings, or your indexes."
author: Subhadip Saha
published: 2026-01-14
updated: 2026-01-14
canonical: https://thatdevguy.in/blogs/postgresql-lying-about-indexes
tags: ["PostgreSQL", "Databases", "Performance", "Backend"]
---

# 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`.

```sql
-- 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.

<Callout type="tip">
Add `BUFFERS` too: `EXPLAIN (ANALYZE, BUFFERS) SELECT ...`. It shows cache hits vs disk reads, which changes everything about how you interpret the output.
</Callout>

## 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.

```sql
-- 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:

```sql
-- 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:

```sql
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
```

And you run:

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

Postgres *can* use this index. But:

```sql
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:

```sql
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.

<Callout type="warn">
MySQL and SQLite handle NULLs in indexes differently. If you're coming from either of those, your assumptions about NULL indexability are probably wrong in Postgres.
</Callout>

## Expression Indexes (The Underused One)

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

```sql
-- 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:

```sql
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.

```sql
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:

```sql
-- 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`.

```sql
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.