Analyze SQL queries for optimization suggestions and performance improvements. Part of the DevTools Surf developer suite. Browse more tools in the Developer Utilities collection.
Use Cases
Rewrite a slow report query that is causing database performance issues.
Identify N+1 query patterns by analyzing query structure before running EXPLAIN.
Convert correlated subqueries to JOINs for better optimizer plan generation.
Optimize pagination queries by replacing OFFSET with keyset (cursor) pagination.
Tips
Avoid SELECT * in production queries — it retrieves unnecessary columns, prevents index-only scans, and breaks queries when schema changes add large columns.
Move filtering conditions into WHERE rather than HAVING where possible — WHERE filters before aggregation, HAVING filters after, so WHERE is faster for non-aggregate conditions.
Use EXISTS instead of IN for correlated subqueries — EXISTS short-circuits at the first match; IN fully evaluates the subquery even when the first match would suffice.
Fun Facts
The cost-based query optimizer, which estimates execution costs to choose the best plan, was first implemented in System R at IBM in the 1970s — the same project that produced SQL itself.
PostgreSQL's query planner was completely rewritten in 1994 for PostgreSQL 6.0 and has received major improvements in nearly every major release, with the 2023 introduction of memoize nodes being one of the largest recent changes.
A poorly written SQL query can be 100x–10,000x slower than the optimized equivalent on the same data — query optimization is one of the highest-ROI database performance activities available.
FAQ
When should I use a subquery instead of a JOIN?
JOINs are generally faster because the optimizer has more flexibility to choose execution order. Use subqueries when the logic cannot be expressed as a JOIN (e.g., correlated subqueries checking existence) or when readability matters more than performance for a one-off query.
How do I optimize queries on large tables without adding indexes?
Partition the table by a common filter column (date, region), materialize expensive aggregations in a separate table updated incrementally, or use database-specific parallel query features. Index addition is usually the better first step.
