1. Analyzing Query Execution Plans
| Operator | Meaning |
|---|
| Seq Scan / Table Scan | Read every row — bad on large tables |
| Index Scan | Use index to find rows, fetch from heap |
| Index Only Scan | All needed cols in index |
| Bitmap Index Scan | Combine multiple indexes |
| Nested Loop / Hash / Merge Join | Join algorithms |
| Sort / Hash Aggregate | Grouping/ordering work |
| Gather (Parallel) | Parallel workers combine results |
Example: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT c.name, SUM(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= NOW() - INTERVAL '30 days'
GROUP BY c.name;
| Symptom | Likely Cause |
|---|
| Seq Scan on big table | Missing index / non-sargable predicate |
| Rows estimate vs actual mismatch | Stale statistics |
| High Buffers: read | Cache miss → I/O bound |
| External sort | work_mem too small |
| Wide rows / TOAST | Detoasting large values |
3. Optimizing WHERE Clause Predicates
| Sargable (good) | Non-sargable (bad) |
|---|
col = 'x' | LOWER(col) = 'x' |
created_at >= '2026-05-01' | YEAR(created_at) = 2026 |
col LIKE 'abc%' | col LIKE '%abc%' |
id = 42 | CAST(id AS TEXT) = '42' |
Note: Functional indexes can save non-sargable patterns: CREATE INDEX ON users(LOWER(email));
4. Using Index Hints and Query Hints
| DB | Hint |
|---|
| MySQL | USE INDEX(idx), FORCE INDEX, IGNORE INDEX |
| Oracle | /*+ INDEX(t idx) */ |
| SQL Server | WITH (INDEX(idx)), OPTION (HASH JOIN) |
| Postgres | No native hints; use pg_hint_plan extension or set planner flags |
Warning: Hints lock plans against future improvements. Use as a last resort after stats and indexes are correct.
| Pattern | Rewrite |
|---|
| SELECT * | List needed cols → enables index-only scan |
| OR conditions | UNION ALL of single-predicate queries |
| NOT IN with NULLs | NOT EXISTS |
| Correlated subquery | JOIN + GROUP BY or window |
| LIMIT + OFFSET | Keyset pagination |
| COUNT(*) on huge table | Approximation: pg_class.reltuples, materialized counter |
6. Optimizing Joins
| Tip | Detail |
|---|
| Index FK columns | Both sides of equi-join |
| Drive from selective side | Filter early |
| Avoid functions on join keys | Prevents index use |
| Tune work_mem | Enables hash joins to stay in-memory |
| Bloom indexes / Bloom join (rare) | Pre-filter probe side |
7. Avoiding Full Table Scans
| Approach | Detail |
|---|
| Add appropriate index | B-tree for equality/range; GIN for arrays/JSON |
| Partition pruning | Filter on partition key |
| Selective WHERE | Lower fraction returned → index wins |
| Cluster (PG) / Clustered index | Physically order rows |
8. Using Query Caching
| Layer | Detail |
|---|
| Prepared statement cache | Driver-level; reuses parsed plan |
| Result cache (app) | Redis/Memcached keyed by query |
| Materialized views | Pre-computed result on disk |
| MySQL query cache | Removed in 8.0 |
| Postgres pg_prewarm | Warm buffer pool after restart |
9. Optimizing Aggregate Queries
| Technique | Detail |
|---|
| Partial aggregation | Pre-aggregate per partition, then combine |
| Parallel aggregate | Multi-worker hashagg (PG, MySQL HeatWave) |
| Approximate | HyperLogLog (approx_count_distinct) for COUNT DISTINCT |
| Materialize rollups | Daily summary tables |
| Avoid DISTINCT in agg | Two-stage GROUP BY often faster |
10. Profiling Query Execution Time
| Tool | Use |
|---|
| pg_stat_statements | Aggregate query stats over time |
| auto_explain | Log slow plans automatically |
| MySQL slow log | long_query_time threshold |
| SQL Server Query Store | Plan + runtime history |
| APM / OpenTelemetry | End-to-end trace with DB span |