Index advisor: heuristic and limits

Inputs

Candidate construction (per table in each scope)

  1. Equality columns: =, IN, = ANY, IS NULL, plus the join key of the table that is probed (the one that appears later in the FROM list).
  2. One range column: <, >, <=, >=, BETWEEN, LIKE 'prefix%'. Only the first is used, because columns after a range column cannot narrow the index scan.
  3. Trailing columns: the ORDER BY columns (when all belong to the table, none is an expression); otherwise GROUP BY columns.
  4. Predicates wrapped in functions (lower(email) = ?), <>, NOT IN, leading-wildcard LIKE and OR groups are not indexable by a plain btree and contribute nothing.
  5. Cap at --max-columns (default 4).

Worked example: WHERE customer_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 20 gives equality [customer_id], range [created_at], trailing [created_at] (already present) and the candidate shop_order(customer_id, created_at).

An ORDER BY with LIMIT and no predicates on the table yields an order-only candidate; with unusable predicates present it does not, since scanning an index in order and filtering is rarely a win.

Dedupe against the schema

A candidate is dropped when an existing plain btree index already serves it: the first k index columns equal the candidate's k equality columns as a set (equality order is irrelevant), and the following columns match exactly. Partial and non-btree indexes never count. Candidates whose equality columns contain a whole unique index are skipped (the lookup returns at most one row). If the new index extends an existing shorter non-unique one, the existing one is listed under supersedes.

Candidates that are prefixes of another candidate on the same table are merged into the longer one, and their benefit is added.

Benefit and redundancy

Benefit is the sum of total time of the fingerprints a recommendation helps. It is an upper bound on time saved, not a prediction: it is the whole statement time, including parts an index cannot speed up.

Redundant existing indexes are btree, non-partial indexes whose columns are a prefix of another index. Identical duplicates keep the PK, then a unique index, then the earlier one. Unique and primary-key indexes are never suggested for dropping, since they enforce a constraint.

Limits