pg-index-optimization

PostgreSQL Index Optimization

This skill guides comprehensive index analysis and optimization for PostgreSQL databases using pgtuner-mcp tools, including HypoPG hypothetical index testing.

When to Use This Skill

Use this skill when the user:

• Asks to optimize or review database indexes
• Wants to find and remove unused or duplicate indexes
• Reports slow queries that might benefit from better indexing
• Asks "what indexes should I create?" or "which indexes can I drop?"
• Mentions high storage usage from indexes or slow write performance
• Wants to test index ideas without creating them in production

MCP Resources Available

• pgtuner://docs/tools -- Full tool reference documentation
• pgtuner://table/{schema}/{table_name}/indexes -- Quick view of existing indexes on a table (lightweight)
• pgtuner://table/{schema}/{table_name}/stats -- Table statistics for context
• pgtuner://query/{query_hash}/stats -- Per-query stats to understand which queries benefit from indexes

Related MCP Prompt

This skill corresponds to the index_optimization MCP Prompt. If the user triggers that prompt, follow this skill's workflow.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Required: pg_stat_statements extension for workload analysis
• Recommended: hypopg extension for hypothetical index testing
• Recommended: pgstattuple extension for index bloat analysis

Pre-Flight Checks

Before starting, check extension availability:

1. Use manage_hypothetical_indexes with action: "check" to verify HypoPG. If unavailable, skip Phase 3 (hypothetical testing) and rely on heuristic-based recommendations.
2. If analyze_index_bloat returns an error about pgstattuple, note that bloat measurements will be estimated rather than exact.

Agent Decision Tree

User asks about indexes
  |
  +--> "Which indexes can I drop?"
  |    --> Start with Phase 1 (cleanup), focus on Step 1.1
  |
  +--> "What indexes should I create?"
  |    --> Start with Phase 2 (missing indexes)
  |
  +--> "Comprehensive index review"
  |    --> Follow all 4 phases in order
  |
  +--> "Test a specific index idea"
  |    --> Skip to Phase 3 (hypothetical testing)
  |
  +--> After finding unused indexes, also check:
       --> Read pgtuner://table/{schema}/{table}/indexes for quick context
       --> Cross-reference: are the "unused" indexes UNIQUE? (warn user)
       --> Cross-reference: how long has the DB been up?