Skip to main content

PostgreSQL Index Scan vs. Seq Scan: Why the Query Planner Ignores Your Index

 You identify a slow query. You identify the missing index. You create the index. You run EXPLAIN ANALYZE, expecting a millisecond response time, only to see the PostgreSQL Query Planner stubbornly performing a Sequential Scan.

The index exists, it is valid, and the query references the indexed column. Yet, PostgreSQL refuses to use it.

This is not a bug. It is a mathematical decision made by the Cost-Based Optimizer (CBO). The planner has calculated that reading the entire table is cheaper than traversing the B-Tree and jumping randomly around the disk to fetch rows. This usually happens for two reasons: High Selectivity or Low Physical Correlation.

The Root Cause: Correlation and Random I/O

To understand why the planner ignores your index, you must understand how PostgreSQL calculates "Cost."

  1. Sequential Scan (Seq Scan): The engine reads table pages linearly from the disk. This is highly efficient for I/O subsystems (especially HDDs) because it minimizes disk head movement (seek time).
  2. Index Scan: The engine traverses the B-Tree to find pointers (TIDs), then accesses the table heap to retrieve the actual row data.

If the data in your table is physically disordered regarding the indexed column, an Index Scan results in Random I/O. The engine might read Page 1, then Page 5000, then Page 2.

PostgreSQL tracks this disorder via the correlation statistic in the pg_stats view.

  • 1.0: Perfectly sorted (ascending).
  • -1.0: Perfectly sorted (descending).
  • 0.0: Completely random.

If your query selects a significant portion of the table (e.g., 20% of rows) and the correlation is near 0, the cost of thousands of random I/O seek operations exceeds the cost of simply reading the table sequentially.

The Setup: Reproducing the Issue

Let's create a table with 1 million rows and a timestamp column with low physical correlation (randomly inserted).

-- 1. Create a dummy table
CREATE TABLE event_logs (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE,
    payload JSONB
);

-- 2. Insert 1 million rows with RANDOM created_at dates to simulate low correlation
INSERT INTO event_logs (event_type, created_at, payload)
SELECT 
    'LOGIN',
    NOW() - (random() * INTERVAL '365 days'),
    '{"status": "ok"}'
FROM generate_series(1, 1000000);

-- 3. Create the index
CREATE INDEX idx_logs_created_at ON event_logs(created_at);

-- 4. Update statistics immediately
ANALYZE event_logs;

Now, let's run a query fetching the last 2 weeks of data.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_logs 
WHERE created_at > NOW() - INTERVAL '14 days';

Result: Depending on your random_page_cost settings and the exact randomness, PostgreSQL will likely choose a Seq Scan or a Bitmap Heap Scan over a pure Index Scan because the created_at data is scattered across the heap.

The Solution

There are three ways to fix this, depending on your infrastructure and data patterns.

Step 1: Check the Correlation Statistic

Before optimizing, confirm that physical disorder is the culprit.

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'event_logs' 
AND attname = 'created_at';

If the result is close to 0 (e.g., 0.05 or -0.02), the planner assumes it has to perform a random disk seek for every single row match.

Fix A: The Physical Reorder (CLUSTER)

If this query is critical and the data is mostly historical (write-once, read-many), you can physically rewrite the table to match the index order. This aligns the heap data with the B-Tree, making Index Scans essentially sequential.

Warning: CLUSTER takes an ACCESS EXCLUSIVE lock. Do not run this on a busy production table without maintenance downtime.

-- Physically reorder the table on disk based on the index
CLUSTER event_logs USING idx_logs_created_at;

-- Refresh statistics
ANALYZE event_logs;

Why this works: After clustering, the correlation statistic will be 1.0. The planner now knows that fetching rows via the index involves reading adjacent pages, drastically lowering the estimated cost.

Fix B: Modernize Cost Constants (SSD Tuning)

PostgreSQL's default configuration assumes rotational Hard Disk Drives (HDD).

  • seq_page_cost default: 1.0
  • random_page_cost default: 4.0

The planner assumes random seeking is 4x more expensive than sequential reading. On modern NVMe SSDs, random seeks are nearly as fast as sequential reads. If you are on SSDs (e.g., AWS RDS gp3), the default penalty is too high, causing the planner to irrationally avoid indexes.

Adjust this globally in postgresql.conf or per session to test:

-- Lower the cost of random seeks to reflect SSD performance
SET random_page_cost = 1.1;

-- Retry the query
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM event_logs 
WHERE created_at > NOW() - INTERVAL '14 days';

Why this works: By lowering random_page_cost, you lower the total estimated cost of the Index Scan. If the math drops below the Seq Scan cost, the planner switches strategies immediately.

Fix C: Covering Indexes (Index Only Scan)

If you cannot lock the table to CLUSTER it, and you only need specific columns, create a Covering Index. This allows PostgreSQL to answer the query entirely from the B-Tree without visiting the heap (the messy, unordered table) at all.

-- Drop the old index
DROP INDEX idx_logs_created_at;

-- Create a covering index that includes the payload
-- Note: This increases index size but eliminates heap lookups
CREATE INDEX idx_logs_covering 
ON event_logs(created_at) 
INCLUDE (payload, event_type);

Why this works: This triggers an Index Only Scan. Since the engine doesn't need to visit the table heap, the correlation of the table data becomes irrelevant. The index itself is always sorted.

Summary

When EXPLAIN shows a Seq Scan despite an existing index:

  1. Check pg_stats: If correlation is near 0, your data is fragmented.
  2. Tune random_page_cost: If you are on SSDs, set this to 1.1. The default 4.0 is archaic.
  3. CLUSTER the table: If downtime permits, physically sort the table to match the index.
  4. Use INCLUDE: Bypass the heap entirely if you can satisfy the query with the index alone.

The Query Planner isn't ignoring your index because it's broken; it's ignoring it because, based on the physics of your disk layout and your configuration, the index is simply the slower path.

Labels: