SQL Query Optimizer

Overview

Rewrite SQL queries for maximum performance by eliminating anti-patterns, restructuring JOINs, leveraging window functions, and applying database-specific optimizations for PostgreSQL and MySQL. This skill takes a slow query and its execution plan as input and produces an optimized version with measurable improvement, along with any supporting index changes needed.

Prerequisites

• The slow SQL query text and its current execution time
• EXPLAIN ANALYZE output (PostgreSQL) or EXPLAIN FORMAT=JSON output (MySQL) for the query
• Table row counts and approximate data distribution for involved tables
• psql or mysql CLI for testing rewrites
• Knowledge of the application's acceptable result ordering and NULL handling requirements

Instructions

1. Examine the original query structure and identify common anti-patterns:
   • SELECT * instead of specific columns (forces unnecessary I/O)
   • WHERE column IN (SELECT ...) that can be rewritten as JOIN or EXISTS
   • DISTINCT used to mask duplicate rows from incorrect JOINs