ThePostgresIndexThatSitsThereDoingAbsolutelyNothing

The Frustrating Reality of Ignored Indexes

You did everything right. You identified a slow query, figured out which column needed an index, ran CREATE INDEX, and waited for the magic to happen. Then you ran EXPLAIN ANALYZE and stared at the output in disbelief. Postgres is still doing a sequential scan — reading every single row in the table like it has all the time in the world. Your index is sitting right there, perfectly formed, completely ignored.

This happens more than people admit. I've spent embarrassing amounts of time debugging this exact situation, and what makes it so maddening is that the fix isn't always obvious. The Postgres query planner — the piece of software responsible for deciding how to execute your query — is actually quite sophisticated. When it ignores your index, it usually has a reason. The problem is that reason might be wrong, outdated, or based on assumptions you didn't realize you were making.

Understanding why this happens requires you to think like the planner thinks. It's not stubborn. It's not broken. It's making a cost-based decision, and somewhere in that decision-making process, something has gone sideways.

How the Query Planner Actually Makes Decisions

The Postgres query planner is essentially a cost estimator. For every query you send it, it considers multiple possible execution strategies and assigns each one a cost — a rough estimate of how expensive that operation will be in terms of I/O (input/output operations, basically how much data gets read from disk) and CPU work. It then picks the strategy with the lowest estimated cost.

Sequential scans aren't inherently bad. If you're fetching 80% of the rows in a table, reading the whole thing in one big sweep is actually faster than jumping around the disk following index pointers. Think of it like reading a book: if someone asks you to find every page that mentions the word "the," you'd just read the whole book. But if they ask you to find the three pages that mention "xylophone," you'd use the index in the back. The planner is trying to make that same judgment call.

The planner bases its decisions on statistics about your data — things like how many rows a table has, how many distinct values a column contains, and how those values are distributed. These statistics are stored in a system catalog called pg_statistic and are surfaced through a friendlier view called pg_stats. Here's the catch: if those statistics are stale or inaccurate, the planner makes bad decisions. And bad decisions often mean your index gets skipped.

The Statistics Problem: When Postgres Doesn't Know What It Doesn't Know

The most common reason an index gets ignored is outdated statistics. Postgres runs a background process called autovacuum that periodically updates these statistics, but it doesn't always run as often as it should — especially on tables that are getting hammered with inserts and deletes.

You can manually refresh statistics on a table with ANALYZE:

ANALYZE your_table_name;

After running that, try your EXPLAIN ANALYZE again. You might be surprised. I've seen cases where a table grew from 10,000 rows to 10 million rows practically overnight during a data migration, and the planner was still making decisions based on the old row count. From its perspective, it thought the table was tiny, so a sequential scan made perfect sense. One ANALYZE later, the index was being used.

You can also check what the planner currently thinks about your data by querying pg_stats directly:

SELECT 
  attname,
  n_distinct,
  correlation,
  most_common_vals,
  most_common_freqs
FROM pg_stats
WHERE tablename = 'your_table_name'
  AND attname = 'your_column_name';

The n_distinct value tells you how many distinct values Postgres believes exist in that column. The correlation value tells you how physically ordered the data is on disk relative to the index order — a value close to 1 or -1 means the data is already in order, which makes index scans more attractive. Pay close attention to these numbers. They're the planner's mental model of your data, and if they're wrong, everything downstream will be wrong too.

Selectivity: Why "Not Selective Enough" Is a Real Thing

Even with perfect statistics, the planner might legitimately decide your index isn't worth using. This comes down to selectivity — which is just a fancy word for how narrowly your query filters the data.

Imagine your users table has a column called country. You've indexed it, but when you query for all users where country = 'US' and 70% of your users are in the US, the planner thinks: why would I jump around following index pointers to fetch 7 million out of 10 million rows? It's faster to just read the whole table sequentially. This is completely rational behavior, even though it feels wrong when you're watching it happen.

The selectivity threshold varies depending on your table size and the planner's cost parameters, but a rough mental model is this: if your query is going to return more than roughly 5-10% of the table, Postgres will often prefer a sequential scan. The index becomes more attractive as the query becomes more specific — filtering for country = 'LI' (Liechtenstein) with maybe 300 users is a completely different story.

This is why composite indexes (indexes that cover multiple columns) and more specific queries unlock index usage. If you're seeing sequential scans, ask yourself: is my WHERE clause actually filtering enough data to make an index worthwhile? Sometimes the answer is genuinely no, and the planner is right.

The Data Type Trap and Implicit Type Casting

Here's one that catches even experienced developers: the data type mismatch. You create an index on a column, write your query, and Postgres silently casts one type to another to make the comparison work. That implicit conversion can completely prevent index usage.

A classic example is indexing a varchar column but querying it with an integer, or having a column defined as text but comparing it to a number. Let's say your order_id column is stored as varchar but somewhere in your application code, you're passing it as an integer:

-- Suppose order_id is VARCHAR, but we're passing a number
-- This might NOT use the index:
SELECT * FROM orders WHERE order_id = 12345;

-- This WILL use the index:
SELECT * FROM orders WHERE order_id = '12345';

The difference is subtle. In the first query, Postgres has to cast every value in the order_id column to an integer to compare it — which means it can't use the index as-is. In the second, the comparison works directly against the stored values.

You can catch this by looking at your EXPLAIN output carefully. If you see a function name wrapped around your indexed column in the filter description — something like (order_id)::integer — that's your clue. The column is being transformed before comparison, which breaks index usage. The fix is almost always to make your query match the column's actual data type.

Functions in WHERE Clauses: The Index Killer

Related to the type casting issue, wrapping an indexed column in a function is one of the most reliable ways to make Postgres ignore your index. This surprises people because the intent feels harmless.

-- This will NOT use an index on the email column:
SELECT * FROM users WHERE LOWER(email) = 'bob@example.com';

-- If you need case-insensitive searches, create a functional index instead:
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- NOW this query will use the index:
SELECT * FROM users WHERE LOWER(email) = 'bob@example.com';

The reason is straightforward: your index contains the raw email values, not the lowercased versions. When you apply LOWER() in the WHERE clause, the planner can't use the raw index to look up values because the transformation changes what it's comparing against. It's like building a library catalog sorted alphabetically, then asking someone to find books while only telling them the Dewey Decimal numbers — different organizational system entirely.

Functional indexes (also called expression indexes) solve this elegantly. You're telling Postgres: "Build an index not on the raw column value, but on the result of this expression." Once that index exists, Postgres recognizes when queries use the same expression and can match them up. I've found this particularly useful for date-based queries too — if you're frequently querying DATE_TRUNC('month', created_at), a functional index on that expression can dramatically change your query plan.

Partial Indexes: When Your Index Is Too Broad

Sometimes the issue isn't that your index is wrong — it's that it covers too much data to be useful. This is where partial indexes come in, and they're criminally underused.

A partial index only indexes rows that meet a specific condition. Say you have a jobs table with millions of rows, but only a few thousand have a status of 'pending'. Queries that look for pending jobs run slowly because the index on the status column covers every row, and with mostly 'completed' and 'archived' rows, the planner might not find it selective enough to bother with.

-- Instead of a full index:
CREATE INDEX idx_jobs_status ON jobs (status);

-- Create a partial index covering only the rows you actually query:
CREATE INDEX idx_jobs_pending ON jobs (status) WHERE status = 'pending';

Now when you query for pending jobs, Postgres has a tiny, tightly focused index to work with. The planner sees immediately that this index only covers a small fraction of the table, which makes it extremely attractive to use. Query times can drop dramatically. The index itself is also physically smaller, which means it's cheaper to maintain and more likely to fit in memory.

Checking Your Work with EXPLAIN ANALYZE

Before and after any index change, you should be running EXPLAIN ANALYZE. This gives you the actual execution plan, not just the estimated one, and shows you real timing numbers. There's a friendlier version too — EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) — which also shows you buffer hit/miss information that tells you whether your data is being read from memory or disk.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE customer_id = 42 
  AND status = 'pending'
  AND created_at > NOW() - INTERVAL '30 days';

Look for the words "Index Scan" or "Index Only Scan" in the output — those mean your index is being used. "Seq Scan" means it isn't. "Bitmap Heap Scan" is a middle ground where Postgres uses the index to find row locations but then fetches the actual data in bulk — it's often the right choice for medium-selectivity queries and is worth understanding rather than treating as a failure.

When you see a sequential scan and you believe an index should be used, you can temporarily force the planner's hand to verify:

SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT ...your query...;
SET enable_seqscan = ON;

This doesn't actually disable sequential scans entirely, but it makes them extremely expensive in the planner's cost model, forcing it to try alternatives. If the query is much faster with this setting, you have evidence that the planner is making a suboptimal choice, and you can investigate why its cost estimates are off.

The Planner Cost Constants: Tunable, Often Misconfigured

Postgres has configuration parameters that tell the planner how expensive different operations are relative to each other. The two most important are random_page_cost and seq_page_cost. The defaults assume you're running on spinning hard drives, where random reads are much more expensive than sequential reads.

If you're running on SSDs or NVMe drives — which is most production databases these days — this default ratio is wrong. The gap between random and sequential I/O is much smaller on flash storage. Keeping the default random_page_cost of 4.0 while running on SSDs makes the planner systematically undervalue index scans.

In my experience, setting random_page_cost to somewhere between 1.1 and 1.5 for SSD-backed instances immediately changes behavior in noticeable ways, encouraging more index usage across the board. You can set this in your postgresql.conf or with ALTER SYSTEM SET random_page_cost = 1.1; followed by a configuration reload. It's one of those small changes with surprisingly large downstream effects — suddenly indexes that were previously ignored start getting used, not because you changed the data or the query, but because the planner's mental model of hardware cost more accurately reflects reality.

When Postgres Is Actually Right

The hardest lesson to accept is that sometimes Postgres is correct and you are wrong. The sequential scan isn't a bug. It's the planner telling you that based on everything it knows, reading every row is genuinely the most efficient strategy.

Before forcing an index with hints or configuration tricks, make sure you're not fighting the planner unnecessarily. Check the actual row counts being returned by your query. Reconsider your data distribution. Ask whether the query itself could be restructured to be more selective. Sometimes the real answer isn't a better index — it's a better query, a different schema design, or accepting that certain operations are just inherently expensive and need to be handled at the application layer with caching or pre-aggregation.

The Postgres query planner has been refined over decades by some very smart people. When it ignores your index, the first assumption should be curiosity, not frustration. Why does it think the sequential scan is cheaper? That question, pursued carefully through statistics and execution plans, almost always leads somewhere useful — either to a real fix, or to a deeper understanding of how your data actually works.