Few things are more confusing to new PostgreSQL users than creating an index, running a query, and seeing PostgreSQL completely ignore it. Instead of using that shiny new index, it chooses a Sequential Scan — a full table read from start to finish.

But PostgreSQL isn’t being stubborn. It’s making a cost-based decision. In this article, we’ll walk through the most common reasons why PostgreSQL chooses a sequential scan, even when an index exists — with clear examples from the Pagila Sample Database.

What a Sequential Scan Actually Is

A sequential scan means PostgreSQL reads every row in the table. It doesn’t consult any index. For small tables this is extremely fast; for larger ones it becomes expensive. But the key point: PostgreSQL only uses an index when it believes it will be cheaper than scanning the table.

1. The Table Is Too Small

This is the #1 reason PostgreSQL ignores indexes. If a table has only a few hundred or a few thousand rows, it’s often cheaper to scan the whole thing than perform random I/O through an index.

Example using customer (599 rows in Pagila):

EXPLAIN
SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = 42;

You’ll likely see:

Seq Scan on customer
  Filter: (customer_id = 42)

Even though customer_id is indexed! PostgreSQL simply knows scanning 599 rows is trivial — faster than consulting an index.

2. The Condition Isn't Selective

PostgreSQL will ignore an index when the condition in your WHERE clause matches a large percentage of the table. In those cases, using the index is more expensive than simply scanning the whole table once.

Here’s a great example using Pagila’s customer table. First, create an index:

CREATE INDEX idx_customer_active ON customer(active);

Now run a lookup for active customers:

EXPLAIN
SELECT customer_id, first_name, last_name
FROM customer
WHERE active = 1;

You’ll still see a sequential scan:

Seq Scan on customer
  Filter: (active = 1)

Even though active has an index, more than half of the table has active = 1 in the Pagila dataset. Fetching hundreds of rows through an index is slower than scanning 599 rows in order, so PostgreSQL chooses the sequential scan.

3. You Wrapped the Column in a Function

If the query applies a function to a column, the index cannot be used unless you created a matching function index.

EXPLAIN SELECT *
FROM customer
WHERE lower(email) = 'MARY.SMITH@sakilacustomer.org';

This requires a function index like:

CREATE INDEX idx_customer_email_lower
ON customer ((lower(email)));

Without it, PostgreSQL must scan the entire table, applying lower() row by row.

4. The Expression Isn't Index-Friendly

Certain predicates simply cannot leverage a B-tree index effectively:

• !=
• NOT LIKE
• IS NOT NULL
• Leading wildcard LIKE (e.g., '%abc')

Example in Pagila:

SELECT *
FROM film
WHERE title NOT LIKE 'A%';

PostgreSQL cannot start scanning an index at “everything NOT starting with A,” so a sequential scan is required.

5. Statistics Are Out of Date

PostgreSQL relies heavily on statistics to estimate row counts. If they’re stale, the planner may incorrectly assume a sequential scan is cheaper.

ANALYZE customer;

If you bulk load, delete, or update a large number of rows, running ANALYZE gives the optimizer the real data distribution so it can make better decisions.

6. The Index Columns Don't Match Your Query

PostgreSQL can only use the leading column(s) of a multi-column index for efficient lookups. The index is physically ordered by those columns, so if your query filters on a later column but not the first one, PostgreSQL usually cannot use the index to jump directly to the matching rows.

Here’s an example using Pagila’s payment table:

CREATE INDEX idx_payment_staff_customer
ON payment (staff_id, customer_id);

This query can use the index because it filters on the leading column:

EXPLAIN
SELECT payment_id, amount, payment_date
FROM payment
WHERE staff_id = 2
  AND customer_id = 25;

This query usually cannot use the index efficiently because it ignores the leading column:

EXPLAIN
SELECT payment_id, amount, payment_date
FROM payment
WHERE customer_id = 25;

The index is ordered by staff_id first. Without filtering on staff_id, PostgreSQL cannot walk the index in order to find the matching rows, so it often falls back to a sequential scan or a bitmap index scan. Bitmap scans can still use the index internally, but they are not as efficient as a true index lookup.

Checking the Plan With EXPLAIN ANALYZE

To know exactly what PostgreSQL is doing, always use:

EXPLAIN ANALYZE SELECT ...;

This shows the actual execution, not just the estimate. It’s your most powerful tool when diagnosing unexpected sequential scans!

Conclusion

When PostgreSQL chooses a sequential scan, it’s not ignoring your index — it’s choosing the path it believes will be fastest. Most of the time it’s correct. Small tables, low-selectivity conditions, function calls, non-indexable expressions, outdated statistics, and mismatched index columns are the most common reasons the planner avoids using an index.

The more you understand these patterns, the easier it becomes to design indexes that PostgreSQL can use and write queries that take advantage of them. Practice on the Pagila database, check your plans often, and soon you’ll be able to predict exactly when PostgreSQL will reach for an index — and when it won’t.