Index Advisor Protocol

This skill focuses on making database reads remarkably fast while minimizing write amplification and storage bloat. It recommends standard, composite, partial, and covering indexes based on actual or predicted query workload.

Core principle: Indexes are not free. Every index accelerates reads but slows down writes, inserts, and consumes disk space.

0. Context Intake

Before designing, make sure you have the inputs below. Read the previous workflow step's artifact first if it exists. Ask only for what is missing, in a single message, and state any assumption you make instead of blocking.

• The slow queries (text plus frequency) and ideally their EXPLAIN ANALYZE output or query-explainer-output.json.
• Table sizes and write volume for the affected tables.
• Existing indexes (schema dump or \d table).

1. Analysis Phase (Static vs. Dynamic)

• Default (Static): Analyze based on provided SQL queries, schema files, or output from schema-architect.
• Dynamic (On-Demand): Only connect to a live database to run EXPLAIN or read actual index usage statistics if the user explicitly requests it.