Ineligible Indexes

When viewing an explain plan in IndeX-Ray, 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, this is augmented by the analyzer and is not part of standard PostgreSQL EXPLAIN output.

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 by the planner before even considering 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 analyzer compares the indexes defined on the scanned table against the requirements of the scan operation. Indexes that are structurally incompatible with the operation are flagged as ineligible. This runs entirely on your machine inside the analyzer container.

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.