Chapter 5 · Watch, then practise
Query Processing & Optimization
Read a query plan as a tree of physical operations and distinguish estimated costs from measured execution.
3 questions · 3 with related videos. Matches are based on playlist titles; broader background matches are labeled.
What to study
- Scan and join operators
- Plan trees
- Cardinality estimates
- EXPLAIN and EXPLAIN ANALYZE
Chapter playlists
Choose a playlist
Notes
QUERY PROCESSING STEPS-INTRODUCTION
Unacademy Computer Science · 7:15
Choose a video · 2 lectures
Query-processing steps and cost-based optimization support the distinction between a query and its physical plan.
1. Logical query, physical plan
Why can the same SQL query have several execution plans?
The result specification does not dictate the access method. A DBMS can choose a sequential scan, an index scan or different join operators and orders. The optimizer compares estimated costs and selects a plan while preserving the query result.
Watch out before Adding Indexes to Your Table, Your Database Optimizer Might not Use them
Hussein Nasser · 11:20
This supplementary lecture specifically discusses why a database optimizer might not use an available index.
2. Index versus scan
Why might an optimizer ignore an available index?
If most rows are needed, repeated index lookups and heap visits may cost more than reading the table sequentially. Estimated row counts, table size and cost settings influence the choice. An index exists to offer another access path, not to guarantee its use.
Postgres Explain Explained - How Databases Prepare Optimal Query Plans to Execute SQL
Hussein Nasser · 10:17
The supplementary PostgreSQL EXPLAIN lecture matches the question about planner costs and execution plans.
3. Read estimates carefully
Does cost=10..500 mean the query takes 500 milliseconds?
No. PostgreSQL planner costs use relative units, not milliseconds. EXPLAIN reports estimates; EXPLAIN ANALYZE executes the statement and reports observed timing and rows. Compare estimated with actual row counts to investigate planning errors, remembering that executing a modifying statement changes data.