Review & Cheat Sheet

[!NOTE] This module explores the core principles of Review & Cheat Sheet, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. Key Takeaways

  • Execution Plans: Use EXPLAIN ANALYZE to see how Postgres executes your query. The planner minimizes estimated cost, not necessarily wall-clock time.
  • Scan Types:
  • Seq Scan: Best for large table reads (>10-20% of data).
  • Index Scan: Best for small lookups (random I/O penalty applies).
  • Index Only Scan: Fastest (no heap access, requires Visibility Map).
  • Bitmap Scan: Hybrid for medium-sized result sets.
  • Join Algorithms:
  • Nested Loop: Good for small outer, indexed inner. O(N*logM).
  • Hash Join: Good for large, unsorted sets. Requires work_mem. O(N+M).
  • Merge Join: Good for pre-sorted inputs. O(N+M) if sorted.
  • Statistics: The planner’s decisions are only as good as its stats (pg_stats).
  • MCV: Most Common Values list for skewed data.
  • Histogram: For range queries.
  • Extended Stats: For correlated columns.

2. Cheat Sheet

EXPLAIN Commands

Command Description
EXPLAIN query Show estimated plan (no execution).
EXPLAIN ANALYZE query Run query and show actual times/rows. Modifies data!
EXPLAIN (BUFFERS, ANALYZE) Show shared buffer hits/reads (I/O).
EXPLAIN (FORMAT JSON) Output in JSON for tools.

Join Strategies

Strategy Complexity Best For Memory
Nested Loop O(N × M) Small N, Indexed M. Low
Hash Join O(N + M) Large N & M, Equality (=). High (work_mem)
Merge Join O(N + M) Sorted Inputs, Range (<, >). Low

System Views

View Purpose
pg_stats Column-level statistics (nulls, distinct, histograms).
pg_class Table-level statistics (pages, tuples).
pg_stat_user_tables Live counters (seq_scans, index_scans, dead_tuples).

3. Interactive Flashcards

Test your knowledge of Postgres query performance concepts.

Card 1 of 5
Front Text
Back Text
Click card to flip

4. Next Steps

You’ve mastered query performance basics! The next logical step is to understand how multiple queries interact. Proceed to Module 04: Concurrency.