Ineligible Indexes

When viewing an explain plan in Query Doctor, scan nodes may include an Ineligible Indexes section marked with an asterisk (*). This lists indexes that exist on the scanned table but cannot be used for that particular operation. Like alternative plans, it comes from the patched planner Query Doctor costs your queries on, and stock PostgreSQL does not report it.

What it shows

Each ineligible index is displayed by name. These are indexes that are defined on the table being scanned but were ruled out before the planner costed them as access paths — they never appear as alternatives because they aren’t viable candidates for the operation at all.

Why an index can be ineligible

An index being ineligible doesn’t mean it’s useless — it means it can’t help with this specific scan. Common reasons include:

• Wrong columns — the index covers columns that aren’t referenced in the query’s WHERE, JOIN, or ORDER BY clauses.
• Wrong operator — the query uses an operator the index type doesn’t support. For example, a B-tree index can’t serve a LIKE '%suffix' pattern or a trigram similarity search.
• Partial index exclusion — a partial index has a WHERE clause that doesn’t match the query’s filter conditions.
• Expression mismatch — the index is on an expression (e.g. lower(email)) but the query filters on the bare column, or vice versa.
• Collation mismatch — the index was built with a different collation than the one the query requires.

How to use this information

Ineligible indexes help answer a specific question: “I have indexes on this table — why isn’t PostgreSQL using them?”

If you see an index you expected the planner to consider listed as ineligible, check whether the mismatch is something you can fix — for example, adjusting the query to use an indexed expression, or creating a new index that matches the actual query pattern.

Conversely, if a table has many ineligible indexes across most of its queries, some of those indexes may be candidates for removal. Unused indexes still cost write performance and storage.

Where it appears

Ineligible indexes are shown on scan nodes — the same nodes that can have alternative plans:

• Seq Scan
• Index Scan
• Index Only Scan
• Bitmap Heap Scan

How it’s generated

The planner reports it itself. It already rules these indexes out while choosing an access path, so the list is the decision it made rather than a comparison run against the plan afterwards.

The planner runs on the Query Doctor instance: a PostgreSQL container matched to your database’s major version, holding your schema and your table statistics and none of your rows. The Collector sends them.

All augmented fields are marked with an asterisk (*) in the UI and include a tooltip noting they are not part of standard PostgreSQL EXPLAIN output.