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.

SQL Optimizer | DevTools Surf

DevTools Surf

Example: SQL Optimizer

A worked example, rendered from real sample data. Sign in to run the tool on your own input.

Input

SELECT * FROM users u LEFT JOIN posts p ON u.id = p.user_id WHERE u.status = 'active' OR u.premium = true ORDER BY u.created_at DESC LIMIT 1000

Output

SQL Query Optimization Analysis
══════════════════════════════════════════════════

❌ ISSUE: Using SELECT *
   → Explicitly list required columns for better performance

⚠ Recommendation: Index columns used in WHERE clause
   → Consider index on: U.STATUS = 'ACTIVE' OR U.PREMIUM = TRUE 

⚠ Recommendation: Consider index on ORDER BY columns

══════════════════════════════════════════════════
Estimated Performance: MODERATE
Suggestions Applied: 2-3 optimizations possible

About SQL Optimizer

SQL Optimizer preview - Developer Utilities tool

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.

Related Developer Utilities Tools

Collection JSON → cURL Git Diff → HTML Regex Visualizer Makefile Explainer Shell Script Linter GitHub Actions Visualizer HAR File Viewer API Response Mocker