Why is PostgreSQL not using my index?

Scope and versions

This guide covers PostgreSQL 16, 17, and 18. Examples use ordinary B-tree indexes and SELECT statements. Decisions depend on schema, data distribution, statistics, configuration, PostgreSQL release, and parameter values.

Start with the plan

Suppose orders has an index on customer_id, one customer owns a large share of the table, and you run:

EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42;

An illustrative plan might look like this:

Seq Scan on orders  (cost=0.00..1820.00 rows=45000 width=24)
  Filter: (customer_id = 42)

This output is illustrative, not measured from a particular database. Seq Scan means PostgreSQL plans to examine the table sequentially. rows=45000 is an estimated cardinality, not a runtime count. Fetching many rows through an index can cost more than reading the table. For node fields and costs, see how to read a PostgreSQL EXPLAIN plan.

Check whether the index can serve this query

• Predicate and operator: Confirm the indexed column uses an operator supported by that index's operator class. A cast, function, collation, or different expression can make an ordinary column index inapplicable. An expression index may suit a query that consistently uses that expression.
• Composite index order: For a multicolumn B-tree, leading columns are generally most useful for restricting the scanned range. Check the actual key order and predicates.
• Partial index predicate: The planner must be able to establish that the query condition implies the index predicate.
• Expected matches: If a condition selects a substantial share of the table, an index scan may require many scattered table-page reads. A sequential scan can be cheaper.
• Statistics: Stale or insufficient statistics can misstate the matching row count. Compare estimates with actual rows where safe and check whether recent data changes are reflected.

PostgreSQL has several index types for different operators and data shapes. An index existing in the catalog does not mean it matches a specific query. See the official PostgreSQL 18 index documentation and examining index usage.

Investigate one hypothesis at a time

1. Capture exact SQL, parameter values, PostgreSQL version, table definition, indexes, and approximate table size.
2. Run plain EXPLAIN. Inspect the scan node's filter, estimated rows, and full plan context.
3. Check index applicability: expression, operator, types and casts, collation, composite key order, and partial-index condition.
4. Check statistics freshness. Run ANALYZE orders; under your operational process; it collects planner statistics and may change future plans. If estimates are the concern, see how to diagnose PostgreSQL row estimate errors.
5. Test a candidate index or query rewrite only with representative data and workload. Do not treat disabling sequential scans as a production fix.

EXPLAIN (ANALYZE, BUFFERS) executes the SELECT. Use an appropriate safe environment; an expensive query still consumes resources while running. Inspect actual rows, loops, buffers, and latency rather than inferring a gain from the node name.

Verify the conclusion

Compare original and candidate plans with the same representative parameter values and data state. Confirm identical results, understand estimates and actual rows, and compare repeated latency measurements. Account for index storage and write overhead. A sequential scan may remain right for a broad query.

Primary sources

• PostgreSQL 18: Examining Index Usage
• PostgreSQL 18: Indexes
• PostgreSQL 18: Using EXPLAIN
• PostgreSQL 18: ANALYZE

These chapters are also available in PostgreSQL 16 and 17 manuals; check the deployed major version.

Editorial review

TuningLens editorial team · Published 29 September 2026

TuningLens beta currently supports MySQL and MariaDB SELECT statements only. PostgreSQL analysis is not available.

TuningLens does not connect to your database or execute submitted SQL. Recommendations require review and testing in your environment.