SQL Query Complexity Analyzer

SQL complexity is driven by: number of JOINs (each adds an exponential factor to the query plan), correlated subqueries (run once per row), CTEs with repeated references, large DISTINCT or GROUP BY operations, missing indexes on JOIN/WHERE columns, and HAVING clauses that filter after aggregation. The optimizer cost estimate and actual rows processed are the true measures.

There is no hard limit, but queries with 5+ JOINs become hard to optimize and understand. The optimizer's search space grows factorially with JOIN count. Queries with 8+ JOINs often hit optimizer heuristics that skip exhaustive plan search. Consider CTEs, materialized views, or denormalization when JOIN counts grow large.

A correlated subquery references columns from the outer query (e.g., WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept)). It executes once per row of the outer query, making it O(n) instead of O(1). Rewrite with JOIN + aggregation or window functions for dramatic performance gains.

CTEs (WITH clauses) improve readability and allow recursive queries. In PostgreSQL, CTEs before PG12 were optimization fences (preventing push-down). Modern PostgreSQL, MySQL 8+, and SQL Server inline simple CTEs. Use CTEs for: recursive queries, reusing the same subquery multiple times, and improving readability. Use subqueries for simple one-off filters.

Common optimizations: (1) Add indexes on JOIN and WHERE columns, (2) Rewrite correlated subqueries as JOINs, (3) Use EXPLAIN ANALYZE to find seq scans, (4) Break complex queries into CTEs for the optimizer, (5) Use covering indexes for frequently queried column combinations, (6) Avoid SELECT * — name columns explicitly, (7) Use LIMIT early in subqueries.