@apilium/sql-optimizer

Analyze SQL queries for performance issues, interpret EXPLAIN plans, recommend indexes, and detect anti-patterns. Supports PostgreSQL and MySQL.

Installation

mayros skill install @apilium/sql-optimizer

Usage

Query Analysis

Ask Mayros to analyze a SQL query:

Analyze this query for performance issues:
SELECT * FROM orders WHERE customer_id = 42;

The skill identifies issues such as full table scans, missing indexes, N+1 patterns, SELECT *, implicit type conversions, and correlated subqueries.

EXPLAIN Plan Interpretation

Provide an EXPLAIN output for detailed analysis:

Interpret this PostgreSQL EXPLAIN output:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...

The skill interprets node types (Seq Scan, Index Scan, Hash Join, Nested Loop), cost estimates, actual timing, buffer usage, and row count mismatches.

Index Recommendations

The skill suggests appropriate index types:

• B-tree: standard index for equality and range queries
• Partial index: filtered index for conditional queries (PostgreSQL)
• Covering index: includes non-key columns to avoid heap fetches
• GIN: for JSONB, arrays, and full-text search (PostgreSQL)
• Hash: for pure equality lookups (PostgreSQL 10+)

Anti-Pattern Detection

Detected anti-patterns with severity levels:

• Critical: N+1 queries
• High: function on indexed column, implicit type conversion, correlated subquery, full table scan, missing index
• Medium: SELECT *, missing LIMIT, leading wildcard LIKE, DISTINCT masking joins, OR on different columns

Semantic Predicates

| Predicate | Description | |-----------|-------------| | sql:optimization_found | Asserted for each optimization opportunity | | sql:antipattern_detected | Asserted for each anti-pattern found | | sql:query_context | Query for optimization history |

License

MIT