AdvancedAdvanced PostgreSQL

Theory: Execution Plan Nodes

The query

SQL
-- Q: Name common EXPLAIN node types.
-- 1. Seq Scan: Scans every row in the table (bad for large tables).
-- 2. Index Scan: Traverses index, then fetches row from table heap.
-- 3. Index Only Scan: Traverses index, gets all needed data from index directly (fastest).
-- 4. Bitmap Heap Scan: Builds bitmap in memory of blocks to visit.
-- 5. Nested Loop: Loops through one table for every row in another (fast for small joins).
-- 6. Hash Join: Builds hash table in memory for joins (fast for large joins).
Tested against PostgreSQL 16

Note

Reading EXPLAIN plans is the most valuable skill for database optimization.