db-indexing

db-indexing (M11)

Indexing is the single heaviest Performance & Scale (axis performance) lever (relational weight 20, Indexación). This module checks whether the indexes that exist match how the data is queried — missing, mis-ordered, or wrong-type indexes turn a sub-millisecond lookup into a sequential scan.

What it checks

1. Composite order (ESR) — multi-column indexes should order columns Equality → Sort → Range. A leading range/low-selectivity column wastes the index.
2. Covering / partial — frequent SELECT a,b WHERE c benefits from an INCLUDE covering index; queries always filtered on a predicate (WHERE deleted_at IS NULL) benefit from a partial index.
3. Specialized types — GIN for jsonb/array/FTS containment, GiST/SP-GiST for geometry/range, BRIN for naturally-ordered append-only columns (timestamps). Flag full-text/geo/JSONB filters served by a plain B-tree (or no index).
4. FK-no-index — every foreign key column should have a covering index; an unindexed FK forces a sequential scan on the referenced side during ON DELETE/ON UPDATE and on joins. This is the highest-yield, lowest-risk index finding.

Score / axis

Feeds performance only (relational Indexación w20; Indexación category in every NoSQL profile).

Tier-0 (static)

Parse DDL/migrations for declared indexes and FKs; cross-reference visible query patterns (ORM where/orderBy, raw SQL) against index columns. Detect FK columns with no matching index prefix, composite indexes with a non-equality leading column, and JSONB/array/FTS/geo predicates with no specialized index. Whether an index is actually used is runtime truth → needs_api at Tier-0.

Tier-1 (verification query) — FK-no-index (Postgres)

SELECT conrelid::regclass AS tbl, conname,
       pg_get_constraintdef(c.oid) AS fk
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (c.conkey[1]) = i.indkey[0]   -- leading index column == first FK column
  );

Method index_check. Each returned row is a confirmed unindexed FK (established). For ESR/covering, EXPLAIN (ANALYZE, BUFFERS) on the real query (method explain_plan, Tier-2) confirms the scan type.