query-optimization-specialist

What problem does it solve?

Identifies and resolves slow or inefficient queries in PostgreSQL/Supabase by diagnosing execution plans, locating missing or suboptimal indexes, correcting query patterns, and recommending schema or query changes to reduce latency and resource usage.

Core Features & Use Cases

• Plan Analysis: Interpret EXPLAIN and EXPLAIN ANALYZE output to find sequential scans, inaccurate row estimates, and expensive operations like large sorts or nested loops.
• Indexing & Schema Guidance: Recommend single-column, composite, partial, or covering indexes and advise on appropriate data types to eliminate unnecessary casts.
• Query & Application Fixes: Detect N+1 patterns, suggest JOIN/EXISTS rewrites, optimize aggregations with indexes or materialized views, and validate changes with re-run plans.
• Use Case: When a Supabase-backed list view is slow, run plan analysis to add the proper composite index for the WHERE and ORDER BY columns and verify improved latency.

Quick Start

Run EXPLAIN ANALYZE on the slow query and provide the full plan, schema of involved tables, and recent statistics so the plan can be analyzed and index or query recommendations returned.