How PostgreSQL Evaluates Execution Plans: Cost Estimation Deep Dive
When diving deep into the EXPLAIN command, you already know what index scans, sequential scans, and hash joins are. But have you ever wondered how exactly the cost numbers are calculated? In this deep dive, we’ll explore the formulas behind PostgreSQL’s cost estimation for the three main scanning approaches: sequential scan, index scan, and bitmap heap scan. 1. Cost-based vs Rule-based Consider this query: SELECT * FROM table_a ta JOIN table_b tb ON ta.id = tb.foreign_id WHERE ta.status = 'active' AND tb.created_at > '2020-12-01'; How would you approach this query? The intuitive flow would be: filter each table first, then join. Make tables smaller first, then start joining. Quite intuitive, right? ...