Query Processing & Execution Plans

Query processing ও execution plan

Read: ~32 min Hard 12 practice problems Live SQLite runner

1. What Happens After You Press Enter?

You type SELECT name FROM students WHERE roll = 17004142 in DBeaver and hit Enter. Three milliseconds later the row appears. What did the database actually do during those 3 ms? It is one of the most beautiful pieces of plumbing in computer science, and unlike concurrency control, you can see it: every modern database has an EXPLAIN command that prints the plan it ran.

In this module we walk the path: parse → bind → optimize → execute. We learn to read execution plans, name the common operators (seq scan, index scan, nested loop, hash join, merge join, sort, hash agg), and use SQLite's EXPLAIN QUERY PLAN right here on this page.

আপনি Enter চাপার পর database চারটি ধাপ পেরোয়: parse (SQL টেক্সটকে syntax tree বানানো), bind (প্রতিটি নাম আসলে কোন টেবিল-কলাম বোঝা), optimize (কোন operator-এর কোন ক্রম সবচেয়ে কম খরচে চালানো যায় বের করা), এবং execute (সেই plan-টি চালিয়ে result return করা)। এই পুরো রহস্যটি দেখা যায় EXPLAIN-এর মাধ্যমে।

2. The Four-Stage Pipeline

Figure 34.1 — চারটি ধাপ: parse, bind, optimize, execute।

2.1 Parse

The parser converts your SQL text into an abstract syntax tree (AST). It catches missing commas, unbalanced parentheses, and other syntax errors. At this stage the database does not know what tables exist; it only knows what valid SQL looks like.

2.2 Bind (a.k.a. Analyze, Resolve)

The binder walks the AST and resolves every identifier against the catalog: does the table students exist? does the column roll belong to it? what is its data type? If you mistyped a column name, this is where the error happens. The output is a fully resolved logical plan: a tree of relational operators (selection σ, projection π, join ⋈, etc.) with no decisions yet about how to do them.

2.3 Optimize

The optimizer is the brain. Given a logical plan, it considers many physical alternatives — for each join, "use a nested loop or a hash join?"; for each scan, "read the whole table or use index idx_roll?"; for each subquery, "materialise or inline?". It assigns a cost (estimated CPU + I/O) to each alternative using statistics from ANALYZE and picks the cheapest. The result is the physical plan — a tree of physical operators with concrete algorithms.

2.4 Execute

Finally the executor walks the physical-plan tree, often using a volcano / iterator model: each operator implements a next() that returns one row at a time. Rows stream up the tree from scans through filters and joins to the final output. Memory use stays bounded.

চারটি ধাপের কাজগুলো আলাদা: parse ধরে syntax error; bind ধরে নাম-কলাম-টাইপ error; optimize খরচ গণনা করে কোন physical operator কোন ক্রমে চালাবে; execute সেই plan অনুযায়ী row স্ট্রিম করে। কোন স্তরে কোন error ধরা পড়ে, এটি জানাও গুরুত্বপূর্ণ।

3. Logical Plan vs Physical Plan

The same logical operator can be implemented by very different physical operators. For example, a logical "join" can become a nested-loop, hash, or merge join — each with wildly different performance:

Logical operator	Possible physical operators	When chosen
Scan (σ + π)	Sequential Scan, Index Scan, Index-Only Scan, Bitmap Scan	Sequential when most rows match; index when few rows match.
Join (⋈)	Nested-Loop Join, Hash Join, Merge Join	Nested when one side tiny; hash for unsorted equality joins; merge when both sides already sorted.
Aggregation (γ)	Hash Aggregate, Sort + GroupAggregate, Streaming Aggregate	Hash for many groups; sort + group when output must be ordered.
Sort (τ)	In-memory Sort, External Merge Sort	In-memory when input fits in `work_mem`; otherwise spills to disk.

একই logical operator অনেকভাবে চালানো যায়। যেমন একটি join — কখনো nested-loop, কখনো hash join, কখনো merge join। সঠিক রূপ নির্ভর করে data-র আকার, sort-অবস্থা, এবং index-এর উপস্থিতির উপর। Optimizer-এর কাজ এই বাছাই করা।

4. The Physical Operator Zoo

4.1 Sequential Scan (Seq Scan)

Read every page of the table from beginning to end. Cost is proportional to table size. Best when the query touches a large fraction of rows — say, more than 5–10%. Indexes don't help here; the random-access cost of jumping through the index would exceed the sequential scan.

4.2 Index Scan

Walk an index (B-tree) to find matching rows, then fetch them from the heap. Best when a small, selective predicate is involved (e.g., WHERE id = 17). For very narrow result sets the speedup is thousands of times.

4.3 Nested-Loop Join

For each row of the outer table, scan the inner table for matches. With an index on the inner side, this becomes indexed nested loop — the workhorse for "give me the orders for this one customer". Without an index, complexity is O(N×M).

4.4 Hash Join

Build phase: read the smaller side into a hash table on the join key. Probe phase: scan the larger side and look up each row. O(N+M). Excellent for big equality joins. The catch: needs memory for the hash table; if it doesn't fit, the DB spills to disk in partitions.

4.5 Merge Join

Both inputs already sorted on the join key (perhaps because of an index). Walk both in parallel, advancing whichever side is behind. O(N+M) and streams output without buffering. The optimizer's favourite when the data is already sorted — common in OLAP / warehouse workloads.

4.6 Sort and Hash Aggregate

Sort is straightforward but expensive — O(N log N) plus possible disk spills. The optimizer avoids it whenever an existing index already provides the order. Hash Aggregate computes GROUP BY by hashing the group keys; Sort + GroupAggregate sorts first and then groups in one pass, used when ORDER BY also wants the same keys.

Figure 34.2 — একটি plan tree: row নিচ থেকে উপরে যায়, output শেষে।

এই operator zoo মুখস্ত রাখুন: Seq Scan পুরো table পড়ে; Index Scan selective query-তে ভালো; Nested-Loop এক পাশ ছোট হলে; Hash Join বড় equality join-এ; Merge Join দু'পাশ আগে থেকে sort থাকলে; Sort ব্যয়বহুল — যেখানে possible এড়ান। Optimizer এই সব option থেকে cheapest plan বেছে নেয়।

5. EXPLAIN — Reading the Plan

Every major DB has a way to print the plan:

Database	Command	Output style
PostgreSQL	`EXPLAIN [ANALYZE] [VERBOSE] sql;`	Indented tree with cost estimates; `ANALYZE` also runs the query and shows actual times.
MySQL	`EXPLAIN sql;` · `EXPLAIN ANALYZE sql;`	Tabular plan; `ANALYZE` available since 8.0.18.
SQL Server	`SET SHOWPLAN_TEXT ON;` / Mgmt Studio's plan viewer	Text or graphical tree.
SQLite	`EXPLAIN QUERY PLAN sql;`	Short, friendly tree-style output.

Let's use SQLite right here. First seed a small Bangladeshi-context schema:

explain_seq_scan.sql

-- No index yet on cgpa, so this becomes a SCAN students.
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE cgpa > 3.7;

Now add an index and re-run — the plan changes:

EXPLAIN QUERY PLAN
SELECT name FROM students WHERE cgpa > 3.7;

Now a join. With no index on enrollments.student_id, SQLite chooses a search strategy that scans one side and probes the primary-key index on the other:

EXPLAIN QUERY PLAN
SELECT s.name, e.course, e.grade
  FROM students s
  JOIN enrollments e ON e.student_id = s.id
 WHERE s.university = 'BUET';

Adding the right index to enrollments.student_id usually flips the plan to an indexed nested-loop:

EXPLAIN QUERY PLAN
SELECT s.name, e.course
  FROM students s
  JOIN enrollments e ON e.student_id = s.id
 WHERE s.university = 'BUET';

5.1 Reading a Postgres-style EXPLAIN ANALYZE

HashAggregate  (cost=312..313 rows=8 width=20) (actual time=4.1..4.2 rows=8 loops=1)
  -> Hash Join  (cost=10..280 rows=12000 width=22) (actual time=0.4..3.7 rows=12104 loops=1)
       Hash Cond: (e.student_id = s.id)
       -> Seq Scan on enrollments e  (rows=12104 loops=1)
       -> Hash  (rows=120)
            -> Index Scan on students_university_idx  (rows=120 loops=1)

How to read it:

Indentation = tree depth. Innermost children run first; their rows feed parents.
cost=startup..total = optimizer's estimated cost in arbitrary units.
rows = estimated row count. Compare against the actual count from ANALYZE — big mismatches signal stale statistics.
loops = how many times the operator was invoked (often >1 inside a nested loop).

EXPLAIN-এর output ভেতরের অপারেটর থেকে বাইরের দিকে পড়ুন। প্রতিটি লাইনে cost, rows, loops থাকে। Estimated rows আর actual rows-এর বড় পার্থক্য মানে statistics পুরোনো হয়ে গেছে — ANALYZE চালালে ঠিক হবে। SQLite-এ EXPLAIN QUERY PLAN ছোট হলেও একই গল্প বলে।

6. A Mental Model: Where Time Goes

When a query is slow, time is going somewhere. There are only a handful of suspects:

Symptom in plan	Likely cause	Typical fix
Seq Scan with low selectivity (you wanted 1 row, got 1 million scanned)	Missing or unused index	Create the right index; check column types match.
Hash Join "spilled" / "Disk:" appearing	Hash table didn't fit in memory	Raise `work_mem` or filter earlier.
Sort with very large rows	ORDER BY on unindexed column	Add matching index; project only needed columns.
Nested Loop with millions of iterations	Optimizer underestimated row count	Run `ANALYZE`; rewrite predicate to be sargable.
Estimated rows ≪ actual rows	Stale statistics or correlated columns	Update stats; consider extended statistics.

একটি slow query analyze করতে গেলে প্রথম কাজ হলো EXPLAIN আউটপুট পড়া এবং উপরের টেবিলের মতো প্যাটার্ন খোঁজা। Module 35-এ আমরা একটি concrete case study দেখব — 10 sec থেকে 10 ms পর্যন্ত নামানো।

7. The Iterator Model — Why Plans Stream

Most engines implement plans as a tree of iterators: each operator exposes open(), next(), close(). The root operator is asked for one row; it asks its child; the child asks its grandchild; eventually a Seq Scan reads one row from disk and bubbles back up. Memory stays constant for any size of input.

HashAgg.next()
  -> Filter.next()
       -> HashJoin.next()
            -> SeqScan(enrollments).next()    ← reads one row
            -> hash-table lookup
       returns one matched row
  groups it
returns one aggregated row

Newer engines (DuckDB, MonetDB, Snowflake) replace this with vectorised execution: each next() returns a batch of 1024 rows instead of one — better CPU cache use and SIMD.

পুরোনো ডেটাবেস (Postgres, SQLite) একটি row একসাথে stream করে — iterator/volcano model। নতুন column-store engine গুলো (DuckDB, Snowflake) vectorised — একটি batch (1024 row) একসাথে। দু'টোর-ই উদ্দেশ্য একই: যত বড় টেবিলই হোক, বেশি memory না খরচ করেই answer return করা।

8. Practice Problems

Lots of runnable EXPLAIN QUERY PLAN exercises. Don't just read the answer — run it, then change something and run again.

শুধু উত্তর পড়বেন না — run করুন, কিছু একটা পাল্টান, আবার run করুন। Plan-এর প্রতিটি অপারেটর কেন এসেছে সেটি বোঝার সবচেয়ে ভালো উপায় হলো নিজে variation চেষ্টা করা।

List the four pipeline stages and which class of error each surfaces.

চারটি stage এবং প্রতিটির error class বলুন।

✨ Show Answer (উত্তর দেখুন)

Parse → syntax errors. Bind → unknown table/column/type errors. Optimize → rare; bad statistics produce slow plans, not errors. Execute → runtime errors (division by zero, FK violation, deadlock).
Run the plan; predict whether SQLite picks a SCAN or SEARCH for WHERE university = 'BUET' without an index.

Index ছাড়া WHERE university='BUET'-এর জন্য SQLite কী বাছবে?
✨ Show Answer (উত্তর দেখুন)
```
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE university = 'BUET';
```
SCAN students — without an index, the entire table is read.
Add an index on university and re-run. Does the plan change?

university-এ index যোগ করে আবার চালান। Plan কি পাল্টায়?
✨ Show Answer (উত্তর দেখুন)
```
EXPLAIN QUERY PLAN
SELECT name FROM students WHERE university = 'BUET';
```
SEARCH students USING INDEX idx_uni — now SQLite uses the index for direct lookup.
When would a Seq Scan be faster than an Index Scan, even with the perfect index available?

পরিপূর্ণ index থাকা সত্ত্বেও Seq Scan কখন বেশি দ্রুত হতে পারে?

✨ Show Answer (উত্তর দেখুন)

When the predicate matches a large fraction of the table — say, 30%+. Index Scan does random I/O for each match; sequential scan is faster overall once selectivity drops below roughly 5–10%. Modern optimizers compute this break-even using statistics.
Run the join plan with and without idx_enr_sid. Which physical join algorithm does each pick?

join plan-টি index সহ ও ছাড়া চালান। কোন physical join algorithm আসে?
✨ Show Answer (উত্তর দেখুন)
```
EXPLAIN QUERY PLAN
SELECT s.name, e.course
  FROM students s
  JOIN enrollments e ON e.student_id = s.id;
```
Without an index on enrollments.student_id, SQLite scans enrollments and uses the integer-PK index on students for each row (indexed nested-loop). With an index on the foreign key, SQLite can pick whichever side is smaller as the outer.
In one paragraph, contrast Hash Join and Merge Join. When does the optimizer pick Merge?

Hash Join আর Merge Join-এর মধ্যে পার্থক্য এক অনুচ্ছেদে বলুন।

✨ Show Answer (উত্তর দেখুন)

Hash Join builds a hash on the smaller side and probes with the larger; cost is O(N+M) but uses memory. Merge Join requires both inputs to be sorted on the join key, then walks them in lockstep with O(N+M) cost and constant memory. The optimizer picks Merge when both sides are already sorted (e.g., delivered by indexes) or when the hash table wouldn't fit in work_mem; otherwise Hash Join is usually faster.
Run an aggregation with EXPLAIN QUERY PLAN; identify the operator name SQLite uses for GROUP BY.

SQLite-এ GROUP BY-র জন্য plan-এ কোন operator?
✨ Show Answer (উত্তর দেখুন)
```
EXPLAIN QUERY PLAN
SELECT course, COUNT(*) FROM enrollments GROUP BY course;
```
SQLite typically uses "USE TEMP B-TREE FOR GROUP BY" — its name for sort-based aggregation. Larger DBs would call this Sort + GroupAggregate or HashAggregate.
A Postgres plan shows rows=12000 but actual rows=2. What action would you take first?

Postgres plan-এ rows=12000, কিন্তু actual=2। প্রথম কাজ কী?

✨ Show Answer (উত্তর দেখুন)

Run ANALYZE table_name; to refresh the optimizer's statistics. Big estimate-vs-actual mismatches are usually stale stats. If the gap persists after ANALYZE, the columns may be correlated — consider CREATE STATISTICS for multi-column distributions.
Define "sargable" predicate. Give one sargable and one non-sargable example.

"sargable" predicate কী? একটি sargable এবং একটি non-sargable উদাহরণ দিন।

✨ Show Answer (উত্তর দেখুন)

"Sargable" = "Search ARGument-able" — a predicate that lets the optimizer use an index. Sargable: WHERE created_at >= '2025-01-01'. Non-sargable: WHERE strftime('%Y', created_at) = '2025' — wrapping the column in a function blocks index use.
Why is the iterator (volcano) model good at low memory but limited on modern CPUs?

Iterator model কেন memory কম খায় কিন্তু আধুনিক CPU-তে সীমাবদ্ধ?

✨ Show Answer (উত্তর দেখুন)

One row per call → constant memory regardless of input size. But each next() is a virtual function call, defeating the CPU's branch predictor and cache. Vectorised execution (a batch of ~1024 rows per call) keeps memory bounded and feeds the CPU efficiently — that's why DuckDB and Snowflake use it.
Run the EXPLAIN twice — once for ORDER BY id, once for ORDER BY cgpa. Why is one cheaper?

দুই ORDER BY-এর জন্য EXPLAIN চালান। একটি কেন সস্তা?
✨ Show Answer (উত্তর দেখুন)
```
EXPLAIN QUERY PLAN SELECT * FROM students ORDER BY id;
EXPLAIN QUERY PLAN SELECT * FROM students ORDER BY cgpa;
```
ORDER BY id is free — the integer PK already provides that order, so no sort. ORDER BY cgpa needs a TEMP B-TREE (sort) unless you create an index on cgpa.
In your own words, explain why an optimizer might be wrong even when statistics are perfect.

পরিপূর্ণ statistics থাকলেও optimizer কেন ভুল করতে পারে?

✨ Show Answer (উত্তর দেখুন)

Cost models assume independence between predicates and uniform distributions. Real data is correlated (e.g., university and city are not independent in BD), and distributions are skewed. The optimizer's row estimates for combined predicates can therefore be off by orders of magnitude even with fresh stats. This is what extended statistics, hints, and CE-feedback systems try to fix.

Summary — Module 34

Every query passes through four stages — parse, bind, optimize, execute. The optimizer turns a logical plan (relational operators) into a physical plan (concrete algorithms) chosen by cost. The operator zoo to know: Seq Scan, Index Scan, Nested-Loop Join, Hash Join, Merge Join, Sort, Hash Aggregate. EXPLAIN (and SQLite's EXPLAIN QUERY PLAN) shows the chosen plan as a tree; reading it teaches you exactly where time goes. Most engines run the plan using the iterator (volcano) model, streaming one row at a time; modern column-stores switch to vectorised batches. Module 35 will use these foundations to take a real 10-second query down to 10 ms.

একটি SQL query চারটি ধাপে চলে — parse, bind, optimize, execute। Optimizer logical plan-কে cost-ভিত্তিতে physical plan-এ রূপান্তর করে। Operator-গুলো (Seq Scan, Index Scan, Nested-Loop, Hash Join, Merge Join, Sort, Hash Agg) মুখস্ত রাখুন। EXPLAIN দিয়ে যে কোনো query-র plan দেখা যায় — সেখানে cost, rows, loops পড়ে বুঝতে হয় কোথায় সময় যাচ্ছে। পরের module-এ আমরা একটি বাস্তব slow query-কে দ্রুত বানাব।

Next Module → Query Optimization — Cost Models & Hints — slow query কে fast করার ব্যবহারিক ধাপ।

Previous (পূর্ববর্তী) Next: Query Optimization