🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🗃️ धडा 10 — Database query performance: प्रत्येक पत्रक वाचायचे, की index वापरायचा?

📍 तुम्ही इथे आहात: 12 पैकी धडा 10 · मागे: lesson-09-queueing · पुढे: lesson-11-web-performance

📦 या ब्रँचमध्ये काय आहे

धडे 01–09, आणि query plans: database query चालवण्याआधी ती कशी चालवायची याचा आराखडा करतो. SQLite मधील EXPLAIN QUERY PLAN (PostgreSQL आणि MySQL मध्ये EXPLAIN) तो आराखडा दाखवतो: प्रत्येक row चा scan, की index मधून search. हा धडा SQLite मधले खरे plans वाचतो, indexes, एक covering index आणि एक expression index जोडतो, आणि index कशामुळे लपतो ते दाखवतो. perf/demo.py मधील database() आणि perf/sim.py मधील laps_db आणि plan.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

Results office मध्ये एका मोठ्या ढिगात 10,000 lap पत्रके आहेत. 📚 एक पालक विचारतात: "धावपटू 42 चे laps काय होते?"

Index नसताना: ऐश्वर्या प्रत्येक पत्रक एकेक करून उचलते, आणि धावपटू 42 ची पत्रके बाजूला ठेवते. 20 शोधण्यासाठी सगळी 10,000 पत्रके. हा scan.
Index असताना: कतरिनाने धावपटू क्रमांकानुसार लावलेली card box बनवली आहे. 🗂️ ऐश्वर्या थेट "42" वर जाते, आणि cards नेमकी कोणती 20 पत्रके आणायची ते सांगतात. हा index वापरून search.
अधिक चांगले card: प्रत्येक card वर lap time आधीच लिहिलेला असेल, तर तिला पत्रके आणायची गरजच नाही. हा covering index.

पण card box नावानुसार जसे लिहिले आहे तसे लावलेली आहे. एखाद्या पालकाने "RUNNER042" असे मोठ्या अक्षरात विचारले आणि तुलना करण्यासाठी ऐश्वर्याला प्रत्येक नाव लहान अक्षरात करावे लागले, तर लावलेल्या box चा काही उपयोग नाही — पुन्हा प्रत्येक card वाचणे. जोपर्यंत ती लहान अक्षरातील नावानुसार लावलेली दुसरी box बनवत नाही तोपर्यंत.

🗺️ आकृती

flowchart LR
    q["❓ WHERE runner_id = 42"] --> none["no index:<br/>SCAN laps — 10,000 rows"]
    q --> idx["index on runner_id:<br/>SEARCH … USING INDEX — 20 rows"]
    q --> cov["index on (runner_id, lap_ms):<br/>USING COVERING INDEX — no table reads"]
    f["WHERE lower(name) = …"] --> scan2["SCAN runners"]
    f --> expr["index ON runners(lower(name)):<br/>SEARCH … (&lt;expr&gt;=?)"]

🗺️ रेखाटलेली आवृत्ती + एक lab: https://school-edh.pages.dev/performance/lesson-diagrams.html#l10

❓ काय

Query planner (optimiser) — database चा तो भाग जो query कशी चालवायची ते निवडतो: कोणता index, joins चा कोणता क्रम, sort करायचे की नाही. तेच SQL, पण कामात खूप फरक.
Full table scan (SQLite मध्ये SCAN laps, PostgreSQL मध्ये Seq Scan, MySQL मध्ये type: ALL) — प्रत्येक row वाचून तपासणे. लहान tables साठी किंवा बहुतेक rows जुळत असताना ठीक; अन्यथा हळू आणि table सोबत वाढणारे.
Index — column values पासून rows पर्यंतची वेगळी, क्रमाने लावलेली रचना (सहसा B-tree). Search tree मधून साधारण log(n) पायऱ्यांत खाली जातो, मग फक्त जुळणाऱ्या rows वाचतो. Indexes फुकट नसतात: प्रत्येक index जागा घेतो आणि प्रत्येक INSERT/UPDATE थोडा हळू करतो.
Covering index — query ला लागणारा प्रत्येक column index मध्येच असतो, त्यामुळे database table ला कधीच हात लावत नाही (SQLite मध्ये USING COVERING INDEX, PostgreSQL मध्ये Index Only Scan).
Composite index (a, b) — आधी a नुसार, मग b नुसार क्रमाने. तो WHERE a = ? ला, आणि sort शिवाय WHERE a = ? ORDER BY b ला मदत करतो; फक्त WHERE b = ? ला मदत करत नाही (leftmost prefix नियम).
Index कशामुळे लपतो — column वर function किंवा अंकगणित (lower(name), runner_id + 0), सुरुवातीला wildcard (LIKE '%042'), वेगवेगळ्या types ची तुलना. Expression index (SQLite 3.9+, PostgreSQL) ने किंवा query पुन्हा लिहून दुरुस्त करा.
Statistics — ANALYZE values कशा पसरलेल्या आहेत ते साठवतो. चांगल्या statistics सह, rows चा मोठा वाटा जुळत असताना planner scan योग्यरित्या निवडू शकतो, कारण तेव्हा index मधून इकडे-तिकडे उड्या मारणे सरळ वाचत जाण्यापेक्षा हळू असते.
Plan चा मजकूर version नुसार बदलतो — हा धडा SQLite 3.51 दाखवतो (SCAN laps); 3.36 पूर्वीचे SQLite SCAN TABLE laps छापत असे, आणि PostgreSQL चा EXPLAIN बराच वेगळा दिसतो.

Database school SQL, joins आणि indexes सुरुवातीपासून शिकवते.

🤔 का

कारण हळू endpoint चे सर्वात सामान्य कारण म्हणजे नसलेला index, आणि table जसजसे वाढते तसतसे ते दररोज अधिक वाईट होते. धडा 04 मधले O(n) विरुद्ध O(log n) नेमके हेच आहे: आज 10,000 rows चा scan पुढच्या वर्षी 1 कोटी (10 million) rows चा scan होतो. Plan वाचल्याने "database हळू आहे" याचे रूपांतर "ही query laps scan करते — हा index जोडा" मध्ये होते.

🔧 कसे (या repo मध्ये)

perf/sim.py मधील laps_db() एक in-memory SQLite database बनवते: 5 दिवसांतील 500 धावपटू आणि 10,000 laps (प्रत्येक धावपटूचे 20). plan(conn, sql, params) EXPLAIN QUERY PLAN चालवते आणि detail column परत देते — SQLite चे स्वतःचे शब्द. vm_steps progress handler वापरून SQLite च्या virtual-machine पायऱ्या मोजते; हे कामाचे ढोबळ मोजमाप तुमच्या SQLite version वर अवलंबून असते (म्हणून ते कधीच नेमके सांगितले जात नाही). perf/demo.py मधील database() एकेक करून indexes जोडते आणि प्रत्येकानंतर plan छापते.

🧪 करून पाहा

python3 perf/demo.py database
python3 - <<'EOF'
import sys; sys.path.insert(0, "perf"); from sim import laps_db, plan, vm_steps
db = laps_db()
q = "SELECT runner_id, MIN(lap_ms) FROM laps WHERE day = ? GROUP BY runner_id"
print("before:", plan(db, q, ("2026-09-21",)))
db.execute("CREATE INDEX idx_laps_day ON laps(day)")
print("after: ", plan(db, q, ("2026-09-21",)))
q2 = "SELECT * FROM laps WHERE runner_id = ? ORDER BY lap_ms"
print("sort:  ", plan(db, q2, (42,)))
db.execute("CREATE INDEX idx_laps_runner_ms ON laps(runner_id, lap_ms)")
print("sorted:", plan(db, q2, (42,)))
q3 = "SELECT id FROM runners WHERE lower(name) = 'runner042'"
db.execute("CREATE INDEX idx_runners_lower ON runners(lower(name))")
print("expr:  ", plan(db, q3))
db2 = laps_db(); a = vm_steps(db2, "SELECT lap_ms FROM laps WHERE runner_id = 42")
db2.execute("CREATE INDEX i ON laps(runner_id)"); b = vm_steps(db2, "SELECT lap_ms FROM laps WHERE runner_id = 42")
print(f"SQLite steps: scan about {a:,} · index about {b:,}   (exact counts depend on your SQLite version)")
EOF

शेवटची ओळ SQLite च्या अंतर्गत पायऱ्या मोजते — SQLite 3.51 सह scan साठी सुमारे 30,000 आणि index साठी 100. दुसऱ्या SQLite version सह तुमचे आकडे वेगळे असू शकतात; मधले अंतर बदलणार नाही.

✅ तपासा — तुम्हाला काय दिसायला हवे

database हे छापते:

── 10,000 laps, 500 runners · EXPLAIN QUERY PLAN for: SELECT lap_ms FROM laps WHERE runner_id = ?
   no index            → SCAN laps   (reads all 10,000 rows)
   index on runner_id  → SEARCH laps USING INDEX idx_laps_runner (runner_id=?)   (jumps to runner 42's 20 rows)
   index on (runner_id, lap_ms) → SEARCH laps USING COVERING INDEX idx_laps_runner_ms (runner_id=?)
   covering: every column the query needs is in the index, so the table itself is never read
   runners WHERE name = 'runner042'         → SEARCH runners USING COVERING INDEX idx_runners_name (name=?)
   runners WHERE lower(name) = 'runner042'  → SCAN runners
   runners WHERE name LIKE '%042'           → SCAN runners

तुमचा snippet असा सुरू होतो:

before: ['SCAN laps', 'USE TEMP B-TREE FOR GROUP BY']
after:  ['SEARCH laps USING INDEX idx_laps_day (day=?)', 'USE TEMP B-TREE FOR GROUP BY']
sort:   ['SCAN laps', 'USE TEMP B-TREE FOR ORDER BY']
sorted: ['SEARCH laps USING INDEX idx_laps_runner_ms (runner_id=?)']
expr:   ['SEARCH runners USING COVERING INDEX idx_runners_lower (<expr>=?)']

🏁 तुम्ही आत्ताच काय सिद्ध केले

फक्त एक index जोडून तेच SQL 10,000 rows वाचण्यावरून 20 वर आले — आणि covering index सह table चे वाचनच शून्य. Composite index (runner_id, lap_ms) ने USE TEMP B-TREE FOR ORDER BY ही पायरीही काढून टाकली: rows index मधून आधीच क्रमाने येतात. Expression index जुळेपर्यंत lower(name) ने name index लपवला होता. day query ला तिच्या GROUP BY साठी अजूनही temporary B-tree लागतो — day वरचा index rows शोधतो, पण त्यांना धावपटूनुसार group करत नाही.

⚠️ नेहमीच्या चुका

EXPLAIN वाचण्याऐवजी indexes बद्दल अंदाज बांधणे
"असू दे" म्हणून प्रत्येक column वर index — हळू writes, अधिक जागा, क्वचितच वापर
query साठी चुकीच्या column क्रमातील composite index
column वर साधा index असताना WHERE lower(email) = ? किंवा WHERE created_at::date = ?
अगदी लहान table वर test करणे, जिथे scan हाच योग्य plan असतो आणि समस्या कधीच दिसत नाही
दोन columns ने covering index ला संपूर्ण काम करू दिले असते तिथे SELECT *

🏭 प्रत्यक्ष वापरात

खऱ्या account वर — PostgreSQL चा EXPLAIN ANALYZE query चालवतो आणि plan शेजारी खऱ्या rows आणि वेळा दाखवतो (म्हणून UPDATE/DELETE बाबत काळजी घ्या — त्यांना अशा transaction मध्ये गुंडाळा जो तुम्ही roll back कराल):

EXPLAIN (ANALYZE, BUFFERS) SELECT lap_ms FROM laps WHERE runner_id = 42;
-- look for: Seq Scan vs Index Scan / Index Only Scan / Bitmap Heap Scan,
-- estimated rows vs actual rows, and "Buffers: shared hit=… read=…"
CREATE INDEX CONCURRENTLY idx_laps_runner ON laps (runner_id);   -- builds without blocking writes
CREATE INDEX idx_runners_lower_name ON runners (lower(name));     -- an expression index
ANALYZE laps;                                                     -- refresh the planner's statistics

आधी हळू queries शोधा. PostgreSQL एका मर्यादेपेक्षा हळू असलेली प्रत्येक query log करू शकतो:

ALTER SYSTEM SET log_min_duration_statement = '200ms';
SELECT pg_reload_conf();

MySQL: EXPLAIN ANALYZE SELECT ... (8.0.18+) आणि slow query log (long_query_time). SQLite, command line वरून:

sqlite3 school.db "EXPLAIN QUERY PLAN SELECT lap_ms FROM laps WHERE runner_id = 42;"

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: तुमच्या सर्वाधिक वेळा चालणाऱ्या दहा आणि सर्वात हळू दहा queries साठी, plan code शेजारी ठेवा, आणि table दहापट वाढल्यावर तो पुन्हा पाहा.

⏭️ पुढे

आता server वेगवान आहे. पण पालक निकाल त्यांच्या phones वर पाहत आहेत. पुढे: web performance — LCP, INP, CLS, payload, compression आणि caching headers.

git checkout lesson-11-web-performance

🗃️ Lesson 10 — Database query performance: read every sheet, or use the index?

📍 You are here: Lesson 10 of 12 · Previous: lesson-09-queueing · Next: lesson-11-web-performance

📦 What's in this branch

Lessons 01–09, plus query plans: before a database runs a query, it plans how. EXPLAIN QUERY PLAN in SQLite (EXPLAIN in PostgreSQL and MySQL) shows the plan: a scan of every row, or a search through an index. This lesson reads real plans from SQLite, adds indexes, a covering index and an expression index, and shows what hides an index. database() in perf/demo.py and laps_db and plan in perf/sim.py.

🧒 Explain like I'm 5

The results office has 10,000 lap sheets in one big pile. 📚 A parent asks: "What were runner 42's laps?"

No index: Aishwarya picks up every sheet, one by one, and keeps the ones for runner
1. All 10,000 sheets, to find 20. That is a scan.
With an index: Katrina made a card box sorted by runner number. 🗂️ Aishwarya jumps to "42", and the cards say exactly which 20 sheets to fetch. That is a search using an index.
A better card: if each card already has the lap time written on it, she does not need to fetch the sheets at all. That is a covering index.

But the card box is sorted by the name as written. If a parent asks for "RUNNER042" in capitals and Aishwarya must lower-case every name to compare, the sorted box is no help — back to reading every card. Unless she makes a second box, sorted by the lower-case name.

🗺️ Diagram

flowchart LR
    q["❓ WHERE runner_id = 42"] --> none["no index:<br/>SCAN laps — 10,000 rows"]
    q --> idx["index on runner_id:<br/>SEARCH … USING INDEX — 20 rows"]
    q --> cov["index on (runner_id, lap_ms):<br/>USING COVERING INDEX — no table reads"]
    f["WHERE lower(name) = …"] --> scan2["SCAN runners"]
    f --> expr["index ON runners(lower(name)):<br/>SEARCH … (&lt;expr&gt;=?)"]

🗺️ Drawn version + a lab: https://school-edh.pages.dev/performance/lesson-diagrams.html#l10

❓ What

Query planner (optimiser) — the part of the database that picks how to run a query: which index, which join order, whether to sort. Same SQL, very different work.
Full table scan (SCAN laps in SQLite, Seq Scan in PostgreSQL, type: ALL in MySQL) — read every row and test it. Fine for small tables or when most rows match; slow and growing with the table otherwise.
Index — a separate sorted structure (usually a B-tree) from column values to rows. A search goes down the tree in about log(n) steps, then reads only the matching rows. Indexes are not free: each one takes space and makes every INSERT/UPDATE a bit slower.
Covering index — the index contains every column the query needs, so the database never touches the table (USING COVERING INDEX in SQLite, Index Only Scan in PostgreSQL).
Composite index (a, b) — sorted by a, then b. It helps WHERE a = ?, and WHERE a = ? ORDER BY b without a sort; it does not help WHERE b = ? alone (the leftmost prefix rule).
What hides an index — a function or arithmetic on the column (lower(name), runner_id + 0), a leading wildcard (LIKE '%042'), comparing different types. Fix with an expression index (SQLite 3.9+, PostgreSQL) or by rewriting the query.
Statistics — ANALYZE stores how values are spread. With good statistics, a planner may correctly choose a scan when a big share of rows match, because jumping around through an index is then slower than reading straight through.
Plan text differs by version — this lesson shows SQLite 3.51 (SCAN laps); SQLite before 3.36 printed SCAN TABLE laps, and PostgreSQL's EXPLAIN looks quite different.

The Database school teaches SQL, joins and indexes from the start.

🤔 Why

Because a missing index is the most common cause of a slow endpoint, and it gets worse every day the table grows. Lesson 04's O(n) vs O(log n) is exactly this: a scan of 10,000 rows today is a scan of 10 million next year. Reading the plan turns "the database is slow" into "this query scans laps — add this index".

🔧 How (in this repo)

laps_db() in perf/sim.py builds an in-memory SQLite database: 500 runners and 10,000 laps (each runner has 20), over 5 days. plan(conn, sql, params) runs EXPLAIN QUERY PLAN and returns the detail column — SQLite's own words. vm_steps counts SQLite's virtual-machine steps with a progress handler, a rough measure of work that depends on your SQLite version (so it is never quoted exactly). database() in perf/demo.py adds indexes one by one and prints the plan after each.

🧪 Try it

python3 perf/demo.py database
python3 - <<'EOF'
import sys; sys.path.insert(0, "perf"); from sim import laps_db, plan, vm_steps
db = laps_db()
q = "SELECT runner_id, MIN(lap_ms) FROM laps WHERE day = ? GROUP BY runner_id"
print("before:", plan(db, q, ("2026-09-21",)))
db.execute("CREATE INDEX idx_laps_day ON laps(day)")
print("after: ", plan(db, q, ("2026-09-21",)))
q2 = "SELECT * FROM laps WHERE runner_id = ? ORDER BY lap_ms"
print("sort:  ", plan(db, q2, (42,)))
db.execute("CREATE INDEX idx_laps_runner_ms ON laps(runner_id, lap_ms)")
print("sorted:", plan(db, q2, (42,)))
q3 = "SELECT id FROM runners WHERE lower(name) = 'runner042'"
db.execute("CREATE INDEX idx_runners_lower ON runners(lower(name))")
print("expr:  ", plan(db, q3))
db2 = laps_db(); a = vm_steps(db2, "SELECT lap_ms FROM laps WHERE runner_id = 42")
db2.execute("CREATE INDEX i ON laps(runner_id)"); b = vm_steps(db2, "SELECT lap_ms FROM laps WHERE runner_id = 42")
print(f"SQLite steps: scan about {a:,} · index about {b:,}   (exact counts depend on your SQLite version)")
EOF

The last line counts SQLite's internal steps — about 30,000 for the scan and 100 for the index with SQLite 3.51. Your numbers may differ with another SQLite version; the gap will not.

✅ Verify — what you should see

database prints:

── 10,000 laps, 500 runners · EXPLAIN QUERY PLAN for: SELECT lap_ms FROM laps WHERE runner_id = ?
   no index            → SCAN laps   (reads all 10,000 rows)
   index on runner_id  → SEARCH laps USING INDEX idx_laps_runner (runner_id=?)   (jumps to runner 42's 20 rows)
   index on (runner_id, lap_ms) → SEARCH laps USING COVERING INDEX idx_laps_runner_ms (runner_id=?)
   covering: every column the query needs is in the index, so the table itself is never read
   runners WHERE name = 'runner042'         → SEARCH runners USING COVERING INDEX idx_runners_name (name=?)
   runners WHERE lower(name) = 'runner042'  → SCAN runners
   runners WHERE name LIKE '%042'           → SCAN runners

Your snippet starts with:

before: ['SCAN laps', 'USE TEMP B-TREE FOR GROUP BY']
after:  ['SEARCH laps USING INDEX idx_laps_day (day=?)', 'USE TEMP B-TREE FOR GROUP BY']
sort:   ['SCAN laps', 'USE TEMP B-TREE FOR ORDER BY']
sorted: ['SEARCH laps USING INDEX idx_laps_runner_ms (runner_id=?)']
expr:   ['SEARCH runners USING COVERING INDEX idx_runners_lower (<expr>=?)']

🏁 What you just proved

The same SQL went from reading 10,000 rows to 20, just by adding an index — and to no table reads at all with a covering index. The composite index (runner_id, lap_ms) also removed the USE TEMP B-TREE FOR ORDER BY step: the rows come out of the index already sorted. lower(name) hid the name index until an expression index matched it. The day query still needs a temporary B-tree for its GROUP BY — an index on day finds the rows, but does not group them by runner.

⚠️ Common mistakes

guessing about indexes instead of reading EXPLAIN
an index on every column "just in case" — slower writes, more space, rarely used
a composite index in the wrong column order for the query
WHERE lower(email) = ? or WHERE created_at::date = ? with a plain index on the column
testing on a tiny table, where a scan is the right plan and the problem never shows
SELECT * when two columns would let a covering index do the whole job

🏭 In production

On a real account — PostgreSQL's EXPLAIN ANALYZE runs the query and shows real rows and times next to the plan (so be careful with UPDATE/DELETE — wrap them in a transaction you roll back):

EXPLAIN (ANALYZE, BUFFERS) SELECT lap_ms FROM laps WHERE runner_id = 42;
-- look for: Seq Scan vs Index Scan / Index Only Scan / Bitmap Heap Scan,
-- estimated rows vs actual rows, and "Buffers: shared hit=… read=…"
CREATE INDEX CONCURRENTLY idx_laps_runner ON laps (runner_id);   -- builds without blocking writes
CREATE INDEX idx_runners_lower_name ON runners (lower(name));     -- an expression index
ANALYZE laps;                                                     -- refresh the planner's statistics

Find the slow queries first. PostgreSQL can log every query slower than a limit:

ALTER SYSTEM SET log_min_duration_statement = '200ms';
SELECT pg_reload_conf();

MySQL: EXPLAIN ANALYZE SELECT ... (8.0.18+) and the slow query log (long_query_time). SQLite, from the command line:

sqlite3 school.db "EXPLAIN QUERY PLAN SELECT lap_ms FROM laps WHERE runner_id = 42;"

🏭 Why this matters in production: for your ten most frequent and ten slowest queries, keep the plan next to the code, and look at it again when the table has grown ten times.

⏭️ Next

The server is fast now. But the parents are looking at the results on their phones. Next: web performance — LCP, INP, CLS, payload, compression and caching headers.

git checkout lesson-11-web-performance

← Previousqueueing Next →web performance

This page is the lesson's README from the lesson-10-database-queries branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.