Database Query Optimization Cheat Sheet

Updated 2026-05-26

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. In 2026, modern databases add AI-driven feedback loops (memory grant feedback, DOP feedback, adaptive joins) that continuously refine plans at runtime, making plan analysis more important than ever.

What This Cheat Sheet Covers

This topic spans 21 focused tables and 196 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: Window Function OptimizationTable 8: Common Performance Anti-PatternsTable 9: Pagination StrategiesTable 10: Caching and MaterializationTable 11: Database Statistics ManagementTable 12: Parallel Query ExecutionTable 13: Table PartitioningTable 14: Batch OperationsTable 15: JSON/JSONB Query OptimizationTable 16: Transaction Isolation and ConcurrencyTable 17: Query Hints and DirectivesTable 18: Intelligent Query ProcessingTable 19: Monitoring and Diagnosis ToolsTable 20: Connection and Resource ManagementTable 21: Denormalization Strategies

Table 1: Execution Plan Analysis

Reading execution plans is the single most important skill in query optimization — every other technique is guesswork without it. Plans reveal how the optimizer actually executes a query: which access methods it chose, how it joins tables, where it spends time, and where its cardinality estimates diverge from reality.

Concept	Example	Description
EXPLAIN	`EXPLAIN SELECT * FROM users WHERE id = 1;`	Shows the query execution plan 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.
EXPLAIN ANALYZE BUFFERS	`EXPLAIN (ANALYZE, BUFFERS) SELECT ...;`	Shows buffer cache hits vs disk reads — reveals I/O patterns and identifies queries missing working memory.
Cardinality Estimate	`Estimated Number of Rows: 1000`	Optimizer's predicted row count at each operation — large discrepancies from actual rows indicate stale statistics or complex predicates causing poor plan choices.
Table Scan (Seq Scan)	`Seq Scan on users`	Reads every row 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.
Index Seek	`Index Seek (NonClustered)`	Uses B-tree navigation to jump directly to matching rows — most efficient access method for highly selective equality or narrow range predicates.
Key Lookup / RID Lookup	`Key Lookup (Clustered)`	• Additional lookup to retrieve columns not included in the index — indicates covering index opportunity • can dominate cost when returning many rows

Table 1: Execution Plan Analysis

Concept	Example	Description
EXPLAIN	`EXPLAIN SELECT * FROM users WHERE id = 1;`	Shows the query execution plan 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.
EXPLAIN ANALYZE BUFFERS	`EXPLAIN (ANALYZE, BUFFERS) SELECT ...;`	Shows buffer cache hits vs disk reads — reveals I/O patterns and identifies queries missing working memory.
Cardinality Estimate	`Estimated Number of Rows: 1000`	Optimizer's predicted row count at each operation — large discrepancies from actual rows indicate stale statistics or complex predicates causing poor plan choices.
Table Scan (Seq Scan)	`Seq Scan on users`	Reads every row 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.
Index Seek	`Index Seek (NonClustered)`	Uses B-tree navigation to jump directly to matching rows — most efficient access method for highly selective equality or narrow range predicates.
Key Lookup / RID Lookup	`Key Lookup (Clustered)`	• Additional lookup to retrieve columns not included in the index — indicates covering index opportunity • can dominate cost when returning many rows