db-indexing
Audit index coverage for the query workload — composite index column order (ESR), covering and partial indexes, specialized types (GIN/GiST/BRIN, FTS, geo, JSONB-GIN), and foreign keys with no covering index. Module M11. Feeds the Performance & Scale score.
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-indexingIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is safe. It is a diagnostic tool designed to audit database indexing by parsing schema files and executing standard PostgreSQL performance queries.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
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
- Composite order (ESR) — multi-column indexes should order columns Equality → Sort → Range. A leading range/low-selectivity column wastes the index.
- Covering / partial — frequent
SELECT a,b WHERE cbenefits from anINCLUDEcovering index; queries always filtered on a predicate (WHERE deleted_at IS NULL) benefit from a partial index. - Specialized types —
GINforjsonb/array/FTS containment,GiST/SP-GiSTfor geometry/range,BRINfor naturally-ordered append-only columns (timestamps). Flag full-text/geo/JSONB filters served by a plain B-tree (or no index). - 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 UPDATEand 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.
Findings
Emit findings per schema/finding.schema.json. Examples:
M11.orders.customer_id_fk_unindexed— FK column with no covering index (severity:3,warn, axisperformance, confidencedirectionalstatic /establishedTier-1,fixable: auto—CREATE INDEX CONCURRENTLY).M11.events.composite_order_esr— leading range column in a composite index (severity:2,warn,directional,fixable: proposed).M11.posts.jsonb_no_gin—jsonbcontainment filter with no GIN index (severity:3,warn,directional,fixable: proposed).
Each finding: evidence.observed quotes the FK/index DDL or query verbatim; verification.reproduce
is the catalog query above (FK-no-index) or an EXPLAIN referencing $DATABASE_URL; expected_impact
is banded + confidence-tagged (no naked %).
Honesty
- A missing index is not always a defect — a tiny lookup table or a write-heavy table can be slower
with an index. Scope severity to table size/access frequency; default
directionalwithout stats. - Never quote a latency/row-count improvement number; magnitude is
high|medium|lowonly. CREATE INDEX CONCURRENTLYisauto-fixable for FK gaps (additive, non-blocking, verifiable); composite reorders and partial indexes areproposedbecause intent may differ.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/hainrixz/claude-db/db-indexing">View db-indexing on skillZs</a>