You already added the index and expected it to be a fast query, but Postgres looked at it and said, “Nah, I’ll do it my way”. Have you ever met a case like this? Yes, I know how that feels to meet a case like that.
In this post, I’ll walk you through why Postgres sometimes ignores your index and how to check why it happened.
Let me share three real cases that I’ve met where Postgres completely ignored my index, even though it existed and looked correct on the surface.
1. The table is too small
For small tables, PostgreSQL often prefers a sequential scan. Reading the whole table is sometimes cheaper than jumping around the index + fetching rows from disk.
I had a small table only a few rows with a simple query like this:
SELECT * FROM products WHERE category = 'electronics';And I add index like this:
CREATE INDEX idx_products_category ON products (category);But when I checked the query plan with:
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'electronics';PostgreSQL showed a sequential scan, not an index scan. The table was just too small. PostgreSQL figured that scanning a few rows from memory was cheaper than using the index and jumping to disk to fetch the actual row.
On very small tables, indexes might not be used and that’s okay! It’s often faster that way.
2. If a significant proportion of the rows are being returned
In another case, I had a query like this:
SELECT * FROM products WHERE category = 'electronics';The products table had around 100,000 rows. I created an index on the category column:
CREATE INDEX idx_products_category ON products (category);But PostgreSQL ignored the index and went with a sequential scan. Here’s the data distribution:
With 85% of the table being ‘electronics’, PostgreSQL saw no point in using the index.
Indexes are helpful when your query filters a small portion of the table. If your condition is too broad, the planner prefers a sequential scan.
Instead of using full index, you can use partial index:
CREATE INDEX idx_products_category_non_electronics
ON products (category)
WHERE category <> 'electronics';Why using partial Index?
- Since the index only includes rows that match a specific condition, it’s much smaller than a full index. Smaller index = faster lookup.
- PostgreSQL can search through a partial index more efficiently, especially when the index is highly selective.
- If your table has a dominant value, full indexes become less helpful. Partial indexes let you focus on minority values that really benefit from indexing.
3. You’re Using a Function on an Indexed Column
I wanted to make product searches case-insensitive, so I wrote a query like this:
SELECT * FROM products WHERE LOWER(name) = 'macbook air';I had already added an index on the name column:
CREATE INDEX idx_products_name ON products (name);But PostgreSQL ignored the index completely, why? Because when you apply a function like LOWER() to a column in your WHERE clause, PostgreSQL can't use a regular index on that column. The function transforms the column values, so the planner sees it as a different expression.
You can fix the index with this query:
CREATE INDEX idx_products_lower_name ON products (LOWER(name));Now PostgreSQL could use the index, and the query performance improved significantly.
Source: