Query Optimization part 1

Your EXPLAIN output shows a cost of 16370.00, but the table only has 6,370 pages. Where does the rest come from? In this first episode of the Database Internals series, we build the query cost model from scratch: pages, disk I/O, and the heap. We calculate the cost on paper first, then verify it against a real PostgreSQL database with EXPLAIN. 📚 Resources Full repo (slides + commands): https://github.com/AbdulRahman-cy/Dat... Slides: https://github.com/AbdulRahman-cy/Dat... Commands used in the video: https://github.com/AbdulRahman-cy/Dat... ⏱ Chapters 00:00 Intro: why EXPLAIN ANALYZE looks overwhelming 00:24 What is query cost? 00:57 How data is stored: disk, B+ trees, and the heap 01:36 Pages: fixed-size blocks of rows (100 rows = 34 pages) 02:12 Disk I/O: why the database reads whole pages 02:41 Why a full heap scan is expensive (34 disk I/Os) 03:57 Cached pages: the upside, and why memory reads aren't free 04:54 Theory: the EMPLOYEE example (blocking factor and block count) 05:35 Theoretical vs practical cost (average case = 5,000 I/Os) 06:46 How an index cuts 5,000 I/Os to about 10 07:27 Hands-on: PostgreSQL in Docker with 1M rows 08:36 Counting pages: expecting a cost of 6,370 09:42 Reading the EXPLAIN output 10:20 Where 16,370 comes from: cpu_tuple_cost 11:57 Row width and why SELECT * hurts 12:34 First look at an index scan 13:52 Next episode: B+ trees 🛠 Quick copy docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d -p 5432:5432 postgres docker exec -it my-postgres psql -U postgres 🗺 Series roadmap Query cost → Indexes (B+ trees) → Index scans, index-only scans, bitmap scans → Query optimization → Transactions and ACID → Concurrency control → WAL → Recovery techniques 🔗 Connect YouTube:    / @abdulrahmanbackend   GitHub: https://github.com/AbdulRahman-cy LinkedIn:   / abdulrahman-tamer-65151b379   If this helped, like the video and subscribe so you don't miss the next episode on indexes. #PostgreSQL #DatabaseInternals #QueryOptimization