Database Query Optimization Cheat Sheet

Updated 2026-03-18

Next Topic: Database Replication and High Availability Cheat Sheet

Database query optimization is the systematic process of improving query execution speed and resource efficiency through analysis of execution plans, strategic indexing, and query rewriting. It sits at the intersection of database administration, application performance engineering, and data architecture, directly impacting user experience and infrastructure costs. The key mental model: the query optimizer makes decisions based on statistics and heuristics, but understanding how it thinks—through execution plans—lets you guide it toward better choices when automatic optimization falls short.

What This Cheat Sheet Covers

This topic spans 17 focused tables and 124 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Execution Plan AnalysisTable 2: Index Types and StrategiesTable 3: Index MaintenanceTable 4: Join OptimizationTable 5: Query Rewriting TechniquesTable 6: Subquery and CTE OptimizationTable 7: Common Performance Anti-PatternsTable 8: Pagination StrategiesTable 9: Caching and MaterializationTable 10: Database Statistics ManagementTable 11: Parallel Query ExecutionTable 12: Table PartitioningTable 13: Batch OperationsTable 14: Query Hints and DirectivesTable 15: Monitoring and Diagnosis ToolsTable 16: Connection and Resource ManagementTable 17: Denormalization Strategies

Table 1: Execution Plan Analysis

Concept	Example	Description
EXPLAIN	`EXPLAIN SELECT * FROM users WHERE id = 1;`	• Shows the query execution plan chosen by the optimizer without executing the query • reveals access methods, join types, and estimated costs.
EXPLAIN ANALYZE	`EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';`	• Executes the query and displays actual runtime statistics including real row counts, execution time, and buffers used • critical for identifying planner estimate errors.
Execution Plan Operators	Index Seek, Hash Join, Sort	• Building blocks of query plans that represent specific operations • understanding operators like Nested Loop, Hash Match, and Sort reveals where queries spend time.
Table Scan (Seq Scan)	`Seq Scan on users`	• Reads every row in a table sequentially • often indicates missing indexes or non-selective queries where most rows match the filter.
Index Scan	`Index Scan using idx_email on users`	• Traverses an index in order, reading multiple rows • used for range queries or when ORDER BY matches index order • slower than seek for highly selective queries.
Index Seek	`Index Seek (NonClustered)`	• Uses B-tree navigation to jump directly to matching rows • most efficient access method for highly selective queries with equality or narrow range predicates.
Key Lookup / RID Lookup	`Key Lookup (Clustered)`	• Additional lookup to retrieve columns not included in the index • indicates opportunity for covering indexes • can dominate query cost when returning many rows.
Nested Loop Join	`Nested Loops ├── Index Seek └── Index Seek`	• Iterates outer input, probing inner input for each row • efficient when outer input is small and inner has an index on join column • $O(n \times m)$ complexity.

Table 1: Execution Plan Analysis

Concept	Example	Description
EXPLAIN	`EXPLAIN SELECT * FROM users WHERE id = 1;`	• Shows the query execution plan chosen by the optimizer without executing the query • reveals access methods, join types, and estimated costs.
EXPLAIN ANALYZE	`EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';`	• Executes the query and displays actual runtime statistics including real row counts, execution time, and buffers used • critical for identifying planner estimate errors.
Execution Plan Operators	Index Seek, Hash Join, Sort	• Building blocks of query plans that represent specific operations • understanding operators like Nested Loop, Hash Match, and Sort reveals where queries spend time.
Table Scan (Seq Scan)	`Seq Scan on users`	• Reads every row in a table sequentially • often indicates missing indexes or non-selective queries where most rows match the filter.
Index Scan	`Index Scan using idx_email on users`	• Traverses an index in order, reading multiple rows • used for range queries or when ORDER BY matches index order • slower than seek for highly selective queries.
Index Seek	`Index Seek (NonClustered)`	• Uses B-tree navigation to jump directly to matching rows • most efficient access method for highly selective queries with equality or narrow range predicates.
Key Lookup / RID Lookup	`Key Lookup (Clustered)`	• Additional lookup to retrieve columns not included in the index • indicates opportunity for covering indexes • can dominate query cost when returning many rows.
Nested Loop Join	`Nested Loops ├── Index Seek └── Index Seek`	• Iterates outer input, probing inner input for each row • efficient when outer input is small and inner has an index on join column • $O(n \times m)$ complexity.