Query Optimization — Cost Models & Hints Advanced

ধীরে চলা কোয়েরিকে ১০০× দ্রুত করা — বাস্তবে

Read: ~40 min Advanced 14 practice problems Performance focus

1. The Optimizer in One Sentence

The previous module showed how the database executes a query. This one is about why it picks one plan over another. A modern cost-based optimizer is a tiny chess engine: given many legal plans, it estimates the cost of each and picks the cheapest.

একই প্রশ্নের অনেক উত্তর-পদ্ধতি (plan) থাকতে পারে। Optimizer-এর কাজ হলো প্রতিটি plan-এর আনুমানিক খরচ বের করে সবচেয়ে সস্তা plan বেছে নেওয়া। ভালো optimizer = ভালো অনুমান।
Why this is hard The optimizer cannot run every plan to time it. It must guess from statistics. A small estimation error in one step can blow up by orders of magnitude after many joins.

2. Selectivity & Cardinality — The Two Numbers That Decide Everything

Two estimates drive virtually every cost-based decision:

  • Cardinality — how many rows a step is expected to produce.
  • Selectivity — for a predicate, the fraction of input rows that survive it. Cardinality after a filter ≈ input cardinality × selectivity.
Example. orders has 1,000,000 rows. The predicate city = 'Dhaka' has selectivity 0.40 (40% of rows). Estimated rows after the filter ≈ 400,000.

Selectivity comes from statistics the database keeps about your data — number of distinct values per column, min/max, and (in advanced systems) histograms. They are refreshed by an ANALYZE command (Postgres, SQLite) or automatically (MySQL, SQL Server).

analyze_demo.sql
CREATE TABLE orders(id INTEGER, city TEXT, amount INTEGER);
INSERT INTO orders VALUES
 (1,'Dhaka',3200),(2,'Dhaka',1500),(3,'Chittagong',2800),
 (4,'Dhaka',3150),(5,'Sylhet',2600),(6,'Dhaka',3350);

-- Tell SQLite to gather statistics about row counts and distributions.
ANALYZE;

-- The query plan can now use those stats.
EXPLAIN QUERY PLAN
SELECT SUM(amount) FROM orders WHERE city = 'Dhaka';
Stale statistics → bad plans If your data has changed substantially since the last ANALYZE, the optimizer is making decisions on outdated reality. A fast query may suddenly take 100× longer overnight after a bulk load. Re-run ANALYZE after large data changes.

3. Inside the Cost Model

Every operator (sequential scan, index lookup, hash join, sort, ...) has a cost formula. A simplified model looks like:

cost = cpu_per_row × rows + io_per_page × pages

The optimizer builds a tree of operators, totals the costs, and the cheapest tree wins.

Example: filter on indexed vs unindexed column

PlanWhat it doesApprox. cost
Sequential scan Read every page; check predicate per row. io × N_pages + cpu × N_rows
Index range scan Use B-tree to jump to matching rows. O(log N) seeks + io × matched_pages

For a predicate that selects 0.1% of rows (selectivity 0.001), the index plan typically wins by orders of magnitude. For one that selects 80% of rows, the seq scan often wins (because index lookups become random I/O all over the disk).

অনেক সময় আশ্চর্য লাগে — "index আছে, তবু sequential scan কেন?"। কারণ rows-এর বড় অংশ যদি predicate-এর সাথে মেলে, তখন index scan আসলে বেশি I/O করে; seq scan-ই সস্তা। Optimizer সেটা স্বয়ংক্রিয়ভাবে বুঝে নেয়।

4. Join Order & Algorithms

For three or more tables, the optimizer must decide both which order to join them and which physical algorithm to use for each join.

AlgorithmBest whenCost
Nested-loop joinInner side has a useful index; one side is small.O(outer × log(inner))
Hash joinBoth sides fit in memory; equality predicate.O(outer + inner)
Merge joinBoth sides already sorted on the join key.O(outer + inner) after sort
Filter early, join late The cheapest plans aggressively push WHERE conditions into each base table before any join — sometimes called predicate push-down. Smaller intermediate results = cheaper joins.
একটি ১০০-row table-এর সাথে ১,০০,০০,০০০-row table join করার সময় ছোট table-কে আগে filter করে নিলে পরবর্তী join অনেক সস্তা হয়। Optimizer স্বয়ংক্রিয়ভাবে এটি করার চেষ্টা করে।

5. Case Study — 10 Seconds → 10 Milliseconds

A real e-commerce dashboard runs:

slow_query.sql
-- The slow query: top customers by spend in Dhaka in the last 7 days.
EXPLAIN QUERY PLAN
SELECT c.name, SUM(o.amount) AS total
FROM     customers c
JOIN     orders    o ON o.customer_id = c.id
WHERE    c.city = 'Dhaka'
  AND    o.placed_at >= '2026-05-01'
GROUP BY c.id, c.name
ORDER BY total DESC;

What the plan exposes (in production, on millions of rows):

  1. Sequential scan on customers — there is no index on city.
  2. Sequential scan on orders — no index on placed_at or customer_id.
  3. Nested-loop join over both unfiltered tables. Disaster.
fast_query.sql
-- Add the right indexes.
CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_orders_cust_date ON orders(customer_id, placed_at);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT c.name, SUM(o.amount) AS total
FROM     customers c
JOIN     orders    o ON o.customer_id = c.id
WHERE    c.city = 'Dhaka'
  AND    o.placed_at >= '2026-05-01'
GROUP BY c.id, c.name
ORDER BY total DESC;

Now the plan uses the city index to find Dhaka customers, then for each one uses the composite index on (customer_id, placed_at) as a clean range probe. From a 10-second sequential disaster to a 10-millisecond targeted lookup — same query, smarter indexes.

একই কোয়েরি, কেবল দুইটি index যোগ করে — তাতেই 1000× গতি বৃদ্ধি। Index-ই query optimizer-এর সবচেয়ে বড় বন্ধু। কিন্তু সঠিক column-এ সঠিক ক্রমে index দিতে হবে।

6. Hints — Use Sparingly, If at All

Most major databases let you force a particular plan with a "hint":

  • Oracle — comment-style hints: SELECT /*+ INDEX(orders idx_date) */ ...
  • SQL Server — WITH (INDEX(idx_date))
  • MySQL — USE INDEX(idx_date) / FORCE INDEX
  • PostgreSQL — no built-in hints; use the pg_hint_plan extension.
  • SQLite — no plan hints. The optimizer takes ANALYZE data and decides.
Modern advice — almost always: don't hint A hint freezes a plan that may be optimal today and pessimal next quarter as data grows. Hints rot. Prefer to fix the cause: missing index, stale statistics, query phrased to defeat the optimizer (e.g., WHERE func(col) = x can't use an index on col). Use hints only as a documented last resort with a follow-up ticket to remove them.

7. The Slow-Query Checklist

  1. Run EXPLAIN / EXPLAIN ANALYZE / EXPLAIN QUERY PLAN. Read it.
  2. Look for sequential scans on big tables — usually a missing index.
  3. Check estimated vs actual row counts. Big mismatch ⇒ run ANALYZE.
  4. Check that filters use indexable expressions. WHERE date(placed_at) = '2026-05-01' disables the index; WHERE placed_at >= '2026-05-01' AND placed_at < '2026-05-02' uses it.
  5. For multi-column predicates, ensure the index column order matches.
  6. Avoid SELECT * when only a few columns are needed — covering indexes can serve index-only scans when the index has all the columns.
  7. For hot read paths, consider materialized views or caches.
  8. Only after all the above: consider a hint, and document why.
ধীরে চলা কোয়েরির জন্য প্রথম পদক্ষেপ — EXPLAIN পড়ুন। ৯০% সমস্যা index, statistics বা non-sargable predicate-এর কারণে হয়।

8. Glossary (শব্দকোষ)

TermMeaningবাংলায়
CardinalityEstimated number of rows produced by a step.একটি ধাপের প্রত্যাশিত row সংখ্যা।
SelectivityFraction of input rows surviving a predicate.একটি predicate-এ যত fraction row বেঁচে যাবে।
StatisticsSummary info about column distributions, refreshed by ANALYZE.Column বিতরণ-এর সারাংশ; ANALYZE দিয়ে আপডেট।
Cost-based optimizerPicks the cheapest plan based on estimated cost.আনুমানিক খরচের ভিত্তিতে সবচেয়ে সস্তা plan বেছে নেয়।
Sargable predicateOne that can use an index — e.g. col >= ?.Index ব্যবহার-উপযোগী predicate।
HintComment / clause that forces a particular plan.Optimizer-কে নির্দিষ্ট plan ব্যবহার করতে বাধ্য করার নির্দেশ।

9. Practice Problems

  1. Show the query plan for SELECT * FROM orders WHERE city='Dhaka'; with no index, then add an index on city and show the plan again.
    Index যোগ করার আগে ও পরে plan তুলনা করুন।
    ✨ Show Answer
    ans1.sql
    EXPLAIN QUERY PLAN SELECT * FROM orders WHERE city='Dhaka';

CREATE INDEX idx_city ON orders(city);
ANALYZE;

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE city='Dhaka';
  2. Why does WHERE date(placed_at) = '2026-05-01' defeat an index on placed_at? Rewrite it to use the index.
    কেন function-wrapped predicate index-কে কাজ করতে দেয় না?
    ✨ Show Answer

    Answer: The index stores the raw placed_at values; date(placed_at) is a function-wrapped expression, and the optimizer cannot translate date(col) = ? into a B-tree range. Rewrite as a sargable range:

    WHERE placed_at >= '2026-05-01'
  AND placed_at <  '2026-05-02'
  3. If customers has 10,000 rows and orders has 10,000,000, which side should the optimizer scan first when joining? Why?
    কোন side-কে আগে scan করা ভালো?
    ✨ Show Answer

    Answer: Apply filters first. After city='Dhaka' on customers (selectivity ~0.4) → ~4,000 customers. Then for each, probe an index on orders(customer_id). We do 4,000 index probes instead of scanning 10M rows. The smaller-after-filter side drives the join.

  4. In one sentence, explain why a query that was fast yesterday became slow overnight after a bulk insert.
    কেন overnight কোয়েরি ধীর হয়ে যায়?
    ✨ Show Answer

    Answer: The optimizer's statistics are stale: it still believes the table is small, picks a nested-loop plan, and that plan blows up against the new row count. Re-run ANALYZE to refresh statistics.

  5. When is a sequential scan actually the right plan?
    কখন seq scan ভালো?
    ✨ Show Answer

    Answer: When selectivity is high (the predicate keeps a large fraction of rows) and/or the table is small enough to fit in memory. Random I/O of an index lookup over millions of rows often costs more than a streaming sequential read.

  6. Apply the slow-query checklist to: SELECT * FROM users WHERE LOWER(email) = '...'. What's wrong, and how do you fix it?
    কোয়েরিটি optimize করুন।
    ✨ Show Answer

    Answer: LOWER(email) defeats a normal index on email. Two fixes:

    1. Store emails in lowercase at insert time and search with WHERE email = '...'. Index works.
    2. Create a functional index: CREATE INDEX idx_email_lower ON users(LOWER(email)); (Postgres / SQLite). The optimizer can now use it for the original query.

Summary — Module 35

The optimizer picks the cheapest plan from many candidates, guided by statistics (refreshed by ANALYZE) and a cost model. Selectivity and cardinality estimates drive everything; small mistakes amplify across joins. Most slow queries are caused by missing indexes, stale statistics, or non-sargable predicates — fix the cause first, hint as a last resort, and always use EXPLAIN to verify your fix actually changed the plan.

Optimizer একটি ছোট chess engine। তার সবচেয়ে ভালো বন্ধু — সঠিক index এবং fresh statistics। সবচেয়ে বড় শত্রু — পুরাতন stats এবং function-wrapped predicate।

Next Module → Storage Internals — pages, buffers, B+ trees: below the SQL layer.

Prev: Query Processing Next: Storage Internals