Fix Postgres Index Not Used: Optimize Slow Queries & Boost Performance

By Krapton Engineering · Reviewed by a senior engineer · Last updated Jul 17, 2026

In the world of high-performance web applications and SaaS products, database efficiency is paramount. A common and frustrating challenge developers face is when PostgreSQL, despite having relevant indexes, opts for a full table scan, crippling query speeds. This isn't just an annoyance; it's a critical bottleneck that can degrade user experience, exhaust server resources, and ultimately impact your business.

TL;DR: PostgreSQL indexes might not be used due to low cardinality, incorrect index types, predicate mismatches, missing composite indexes, or outdated statistics. Use EXPLAIN ANALYZE to diagnose, then apply targeted indexing strategies and maintain database statistics to optimize slow queries and ensure indexes are effectively utilized.

Key takeaways

• Diagnose with EXPLAIN ANALYZE: This essential tool reveals PostgreSQL's query plan, showing exactly why an index is ignored and identifying performance bottlenecks.
• Address Common Pitfalls: Issues like low data cardinality, function calls in predicates, or data type mismatches frequently prevent index usage.
• Master Composite Indexes: Often overlooked, composite indexes are crucial for optimizing multi-column queries and eliminating N+1 problems, especially with ORMs.
• Consider Advanced Strategies: Partial indexes and covering indexes offer significant performance gains for specific, complex query patterns.
• Maintain Database Health: Regular VACUUM ANALYZE operations are vital for keeping PostgreSQL's query planner informed and ensuring optimal index selection.

The Frustration of "Postgres Index Not Used"

You've meticulously added indexes to your PostgreSQL tables, expecting lightning-fast queries, only to discover a critical report or a user-facing dashboard is still agonizingly slow. The culprit? PostgreSQL is performing a sequential scan (a full table scan) instead of leveraging your carefully crafted index. This scenario, where a "postgres index not used" message might as well be flashing, is a common source of despair for backend developers.

Why does this happen? PostgreSQL's query planner is sophisticated, but it's not omniscient. It makes decisions based on various factors, including data distribution, table statistics, and the specific query predicates. Sometimes, its calculated cost for an index scan is higher than a sequential scan, or the index simply isn't suitable for the query at hand. In a recent client engagement building a global logistics platform, we observed a critical API endpoint experiencing 5-second response times, despite an index existing on the order_id column. The root cause, as we discovered, was a combination of an inefficient WHERE clause and an outdated index that wasn't properly covering the query's sorting needs.

Diagnosing the Problem with EXPLAIN ANALYZE

The first and most critical step in fixing a "postgres index not used" problem is diagnosis. PostgreSQL provides an invaluable tool for this: EXPLAIN ANALYZE. This command executes your query and then shows you the query planner's chosen execution plan, along with actual runtime statistics.

Here's how to use it:

EXPLAIN ANALYZE
SELECT id, name, created_at
FROM users
WHERE email LIKE 'user%@example.com'
ORDER BY created_at DESC;

The output of EXPLAIN ANALYZE is dense, but key elements to look for include:

• Seq Scan vs. Index Scan (or Index Only Scan): If you see Seq Scan where you expect an index to be used, you've found your problem.
• cost=: This represents the planner's estimated cost (an arbitrary unit) to execute the operation. The first number is startup cost, the second is total cost.
• rows=: The estimated number of rows returned by the operation. A significant discrepancy between estimated and actual rows can indicate stale statistics.
• actual time=: The real-world time taken for the operation, broken into startup and total.
• loops=: How many times this particular plan node was executed.

By carefully examining the plan, you can pinpoint where the planner deviates from your expectations and why an index is being ignored. Often, the WHERE clause conditions are the primary driver of index usage decisions.

Common Reasons Your Postgres Index Isn't Used (and How to Fix Them)

Low Cardinality / Insufficient Data

If a column has very few unique values (e.g., a boolean is_active column), PostgreSQL's planner might determine that scanning the entire table is faster than traversing an index and then fetching rows. An index is most effective when it can quickly narrow down a large dataset.

• Fix: For low-cardinality columns, indexes are often not beneficial for filtering alone. Consider a partial index if specific, highly selective values are frequently queried.

Incorrect Index Type

PostgreSQL offers various index types (B-tree, GiST, GIN, SP-GiST, BRIN), each optimized for different data types and query patterns. A B-tree index, the default, is excellent for equality and range queries on scalar data, but it won't help with full-text search or geospatial queries.

• Fix: Ensure your index type matches your query pattern. For example, use a GIN index for full-text search (@@ operator) or JSONB path queries (@? operator), and a GiST index for geospatial data (PostGIS).

Predicate Mismatch / Function Calls

Applying functions to indexed columns in your WHERE clause often prevents index usage because the function's output isn't indexed. For example, WHERE lower(email) = '...' will ignore an index on email.

• Fix: Rewrite queries to avoid functions on indexed columns, or create expression indexes (functional indexes) on the function's output, e.g., CREATE INDEX ON users (lower(email)).

Missing Composite Indexes (The N+1 Killer)

One of the most common performance killers, especially when using ORMs, is the N+1 query problem, often exacerbated by a lack of appropriate composite indexes. If you frequently query on multiple columns in your WHERE clause (e.g., WHERE status = 'active' AND region = 'EMEA'), a single-column index on status or region might not be enough. PostgreSQL might use one index, then perform a bitmap heap scan or filter the remaining results, which can be inefficient.

-- Problem: Slow query on multiple columns, single indexes exist
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2026-01-01';

-- Solution: Create a composite index
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

On a production rollout we shipped for a fintech client, the failure mode was a dashboard loading hundreds of individual customer transaction lists. Each list triggered a query like SELECT * FROM transactions WHERE customer_id = ? AND created_at BETWEEN ? AND ?. While indexes existed on customer_id and created_at separately, the lack of a composite index on (customer_id, created_at) led to hundreds of inefficient index scans followed by filtering, rather than a single, highly selective composite index scan. Implementing this simple composite index reduced dashboard load times from 15+ seconds to under 2 seconds.

Outdated Statistics / VACUUM Issues

PostgreSQL's query planner relies on statistics about data distribution to make informed decisions. If these statistics are stale, the planner might misestimate costs and choose a suboptimal plan, ignoring a perfectly good index.

• Fix: Ensure regular VACUUM ANALYZE or ANALYZE operations are running. Autovacuum typically handles this, but manual intervention might be needed for rapidly changing tables or after large data imports.

Data Type Mismatches

Implicit type conversions in your WHERE clause can prevent index usage. For example, comparing a numeric column to a string literal (e.g., WHERE user_id = '123' if user_id is an integer) can force a sequential scan.

• Fix: Always ensure the data type of your literal matches the column type. Cast explicitly if necessary, but ideally, avoid implicit conversions.

Advanced Indexing Strategies and Trade-offs

Partial Indexes

A partial index only indexes a subset of rows in a table, defined by a WHERE clause. This can significantly reduce index size and improve performance for queries that frequently target that specific subset.

• Use Case: Indexing only active users (CREATE INDEX ON users (email) WHERE is_active = TRUE).

Covering Indexes (Index-Only Scans)

A covering index includes all the columns required by a query's SELECT and WHERE clauses, allowing PostgreSQL to perform an "index-only scan" without ever touching the main table. This is extremely fast as it avoids disk I/O to the heap.

• Use Case: SELECT id, name FROM products WHERE category = 'electronics' could use a covering index on (category, id, name).

When NOT to use this approach

While indexes are powerful, adding too many can hurt performance. Each index consumes disk space, and more importantly, incurs a write overhead: every INSERT, UPDATE, or DELETE operation must also update all associated indexes. This can slow down write-heavy workloads. Over-indexing also increases the planning time for complex queries. Always prioritize indexes for your most critical and frequently slow read queries, and monitor their actual usage.

ORMs and Their Role in Query Performance

Object-Relational Mappers (ORMs) like Prisma, Drizzle, or TypeORM simplify database interactions, but they can also abstract away the underlying SQL, making it harder to spot performance issues. N+1 queries are a classic example where an ORM's default eager loading or lazy loading behavior can generate many inefficient queries instead of one optimized join or a single query leveraging a composite index.

When using an ORM, be vigilant about:

• select clauses: Explicitly select only the columns you need to enable index-only scans where possible.
• Eager loading: Use ORM features to eagerly load related data in a single query (e.g., .include() or .populate()) rather than fetching each related item individually.
• Raw SQL escape hatches: Don't be afraid to drop down to raw SQL for complex, performance-critical queries that an ORM might struggle to optimize. Many ORMs provide mechanisms for this, allowing you to leverage specific PostgreSQL features like Common Table Expressions (CTEs) or window functions efficiently. For complex scalable API development, raw SQL can be an essential tool.

FAQ

Why does PostgreSQL sometimes prefer a sequential scan over an index scan?

PostgreSQL's query planner estimates the cost of different execution plans. If the table is small, or if the query needs to retrieve a large percentage of the table's rows, a sequential scan might be estimated as faster because it avoids the overhead of index traversal and random disk I/O.

How often should I run VACUUM ANALYZE?

For most workloads, PostgreSQL's autovacuum daemon handles this automatically. However, for tables with very high write volumes or after significant data changes (e.g., large imports), a manual ANALYZE or VACUUM ANALYZE might be beneficial to update statistics immediately, ensuring the planner has the freshest data.

Can adding too many indexes hurt performance?

Yes. While indexes speed up reads, they slow down writes (INSERT, UPDATE, DELETE) because each index must also be updated. They also consume disk space and can increase query planning time. It's crucial to balance read performance gains against write overhead.

What is a covering index?

A covering index is an index that includes all the columns required by a specific query's SELECT and WHERE clauses. This allows PostgreSQL to perform an "index-only scan," retrieving all necessary data directly from the index without needing to access the main table, significantly boosting read performance.

Scale Your Database with Krapton's Expertise

Diagnosing and resolving "postgres index not used" issues requires deep database expertise and a thorough understanding of query optimization. If your team is struggling with slow PostgreSQL queries or complex database performance bottlenecks, Krapton's principal-level software engineers are here to help. We specialize in optimizing database layers, designing scalable schemas, and implementing robust backend solutions for startups and enterprises globally. Don't let database performance hinder your growth; book a free consultation with Krapton to explore how we can elevate your application's performance. For specialized needs, you can also hire expert Node.js developers from our team.