skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
hainrixz/claude-db77 installs

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-indexing
view source ↗

Is 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?

<!-- Copyright (c) 2026 Enrique Rocha (@soyenriquerocha) · tododeia.com · id er-f37b7121 -->

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.

Findings

Emit findings per schema/finding.schema.json. Examples:

  • M11.orders.customer_id_fk_unindexed — FK column with no covering index (severity:3, warn, axis performance, confidence directional static / established Tier-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 — jsonb containment 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 directional without stats.
  • Never quote a latency/row-count improvement number; magnitude is high|medium|low only.
  • CREATE INDEX CONCURRENTLY is auto-fixable for FK gaps (additive, non-blocking, verifiable); composite reorders and partial indexes are proposed because intent may differ.

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>