This document is a practical checklist for SQL query optimization.
- It focuses purely on query-level techniques (not infrastructure, caching layers, or sharding).
The goal is to help developers:
- Write efficient queries from the start.
- Debug and fix slow queries systematically.
- Understand which optimizations matter most (prioritized: ⭐ High → ⚡ Medium → 🟢 Low).
Tip: Always start with query planning (EXPLAIN / EXPLAIN ANALYZE) when investigating performance issues. This shows how the database executes your query (index scan, sequential scan, join order, etc.), which often reveals the root cause of slowness.
| Title | Priority | Remarks |
|---|---|---|
| Use Proper Indexing | ⭐ High | Create appropriate single, composite, partial or covering indexes. - Avoid over-indexing (slows writes). |
Avoid SELECT * |
⭐ High | Fetch only needed columns to reduce I/O and improve query planner decisions. |
| Optimize WHERE Clauses | ⭐ High | Use index-friendly conditions. - Avoid functions on indexed columns. |
| Use Joins Efficiently | ⭐ High | Ensure join keys are indexed. Avoid unnecessary outer joins or Cartesian products. |
| Regular Vacuum / Analyze (Postgres) | ⭐ High | Prevent table/index bloat, update planner statistics. |
| Optimize Subqueries | ⚡ Medium | Prefer JOINs or CTEs over correlated subqueries when possible. |
Use EXISTS Instead of IN (Subqueries) |
⚡ Medium | EXISTS is faster on large datasets; IN can work for small, static lists. |
| Avoid Redundant Data Retrieval | ⚡ Medium | Eliminate unnecessary columns, joins, or repeated calculations. |
Use UNION ALL Instead of UNION |
⚡ Medium | Avoid duplicate-elimination overhead when uniqueness is not required. |
Avoid Unnecessary DISTINCT |
⚡ Medium | Sometimes GROUP BY or window functions are better. |
| Avoid Unnecessary Ordering/Grouping | ⚡ Medium | Only sort/aggregate when required. - Indexes on sort columns can help. |
| Break Down Complex Queries | 🟢 Low | Use stepwise queries, temporary tables, or materialized views for readability and speed. |
| Prevent JSON/JSONB Columns for Filters | 🟢 Low | Hard to index, slow for filtering. Normalize when possible. |
| Batch Inserts/Updates | 🟢 Low | Send multiple rows in one statement instead of row-by-row. |
- SQL Query Optimization: 15 Techniques for Better Performance
- SQL Tunning - Donnemartin
- Understanding caching in Postgres - An in-depth guide
- Tuning Input/Output (I/O) Operations for PostgreSQL
- Secret To Optimizing SQL Queries - Understand The SQL Execution Order
- Analyzing Amazon RDS Database Workloads with Performance Insights
- How does SQL trigger affect performance?