{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"indexes-and-slow-queries","title":"Why a query is slow, and what an index does","url":"https://addaly.com/learn/data-and-sql/indexes-and-slow-queries","position":55,"of":66,"minutes":8,"answer":"An index lets the database skip rows; anything that hides a column's raw value takes that ability away.","keyIdea":"An index lets the database skip rows; anything that hides a column's raw value takes that ability away."},"body":{"format":"markdown","text":"## Slow almost always means reading too much\n\nA database with no help reads every row of the table and checks each one. Four million payment rows at 200 bytes each is about 800 MB of reading to answer a question whose answer is six rows. Even from memory that is not free. From disk it is seconds.\n\nAsk the database what it is doing rather than guessing:\n\n```sql\nEXPLAIN ANALYZE\nSELECT id FROM payments WHERE student_id = 4412;\n\n-- Seq Scan on payments  (cost=0.00..84210.00 rows=1 width=8)\n--   Filter: (student_id = 4412)\n--   Rows Removed by Filter: 3999994\n--   Planning Time: 0.1 ms\n--   Execution Time: 612.4 ms\n```\n\n`Seq Scan` means it read the whole table. `Rows Removed by Filter: 3999994` means it threw away almost all of it. That line is the confession.\n\n## What an index actually is\n\nAn index is a second structure, normally a B-tree, holding the values of one or more columns in sorted order, each with a pointer to where the full row lives. Because it is sorted, the database can binary search it. Four million rows becomes about 22 comparisons instead of four million.\n\n```sql\nCREATE INDEX ON payments (student_id);\n\n-- Index Scan using payments_student_id_idx on payments\n--   (actual time=0.021..0.033 rows=6 loops=1)\n--   Execution Time: 0.08 ms\n```\n\n612 ms to 0.08 ms. That is the whole trick, and it is a big trick.\n\nIndexes are not free. Every INSERT, UPDATE and DELETE must update every index on the table. They take disk space. A table with twelve indexes that is written constantly will be slow to write and its indexes will mostly go unused. Index the columns you filter and join on, not every column you have.\n\n## When the index sits there unused\n\n**You hid the raw value behind a function.**\n\n```sql\nWHERE date(created_at) = '2026-03-14'   -- index on created_at is useless\nWHERE lower(email) = 'ada@example.com'  -- index on email is useless\n```\n\nThe index stores `created_at`, not `date(created_at)`. To compare, the database must compute the function for every row first, which means reading every row. Rewrite as a range, or build an index on the expression itself:\n\n```sql\nWHERE created_at >= '2026-03-14' AND created_at < '2026-03-15'\nCREATE INDEX ON users (lower(email));\n```\n\n**A leading wildcard.** `LIKE '%kumar'` cannot use a B-tree, because a sorted list does not help you find things by their ending. `LIKE 'kumar%'` can.\n\n**The filter matches most of the table.** `WHERE is_active = true` when 95% of rows are active. Following the index and then fetching 95% of the rows one at a time is slower than reading the table straight through. When the planner ignores your index here, it is right and you are wrong.\n\n**The table is small.** Scanning 500 rows takes microseconds. No index will beat that.\n\n## The wins that are usually bigger than an index\n\n**Return less.** Add the missing filter. Add a LIMIT. A query that returns 400,000 rows to an application that displays 20 is slow because of the 399,980, not because of the plan.\n\n**Stop using `SELECT *`** on wide tables. If one column holds a 40 KB JSON blob, selecting it for every row means moving hundreds of megabytes across the network to display a name and a date.\n\n**Do not run one query per row.** An application that fetches 900 orders and then runs one query per order to get the customer has made 901 round trips. At 3 ms each that is nearly three seconds, and the database will report every individual query as fast. One join does it in 12 ms. This is the most common performance bug in real software and it never shows up as a slow query in the logs.\n\n## Reading a plan without being an expert\n\nFind the node with the largest `actual time`. That is your problem, not the scary-looking one at the top. Then compare the estimated `rows=` against the `actual rows=`. If the planner expected 3 rows and got 900,000, it chose its whole strategy on a bad guess: usually stale statistics (run `ANALYZE`) or a filter it cannot reason about, like a function on a column."},"check":{"question":"A table has an index on `created_at`, a timestamp. The query `WHERE date(created_at) = '2026-03-14'` still takes eight seconds. Why is the index not helping?","options":[{"text":"The index has gone stale after so many inserts and needs to be rebuilt","correct":false,"tempting":"Assumes indexes drift out of date, when in fact they are maintained on every write."},{"text":"The index stores the raw timestamps, so the database must compute date() on every row before it can compare anything","correct":true},{"text":"A timestamp is being compared to a string, and the type mismatch prevents the index from being used","correct":false,"tempting":"Reaches for a type mismatch, a real cause of index problems elsewhere, when the literal here would be cast fine."},{"text":"The planner decided the table was too small to be worth an index lookup","correct":false,"tempting":"Applies a genuine planner behaviour to a case where the table is large and the filter is highly selective."}]},"prev":{"slug":"leakage-in-a-join","title":"Leakage: when a feature knows the future","url":"https://addaly.com/learn/data-and-sql/leakage-in-a-join"},"next":{"slug":"create-table-and-constraints","title":"CREATE TABLE: a constraint is a test that runs on every write","url":"https://addaly.com/learn/data-and-sql/create-table-and-constraints"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Why a query is slow, and what an index does', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/indexes-and-slow-queries (Accessed: 31 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Why a query is slow, and what an index does","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"An index lets the database skip rows; anything that hides a column’s raw value takes that ability away.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"slow-almost-always-means-reading-too-much","runs":[{"text":"Slow almost always means reading too much"}]},{"t":"p","runs":[{"text":"A database with no help reads every row of the table and checks each one. Four million payment rows at 200 bytes each is about 800 MB of reading to answer a question whose answer is six rows. Even from memory that is not free. From disk it is seconds."}]},{"t":"p","runs":[{"text":"Ask the database what it is doing rather than guessing:"}]},{"t":"code","lang":"sql","label":"SQL","text":"EXPLAIN ANALYZE\nSELECT id FROM payments WHERE student_id = 4412;\n\n-- Seq Scan on payments  (cost=0.00..84210.00 rows=1 width=8)\n--   Filter: (student_id = 4412)\n--   Rows Removed by Filter: 3999994\n--   Planning Time: 0.1 ms\n--   Execution Time: 612.4 ms"},{"t":"p","runs":[{"text":"Seq Scan","code":true},{"text":" means it read the whole table. "},{"text":"Rows Removed by Filter: 3999994","code":true},{"text":" means it threw away almost all of it. That line is the confession."}]},{"t":"h2","id":"what-an-index-actually-is","runs":[{"text":"What an index actually is"}]},{"t":"p","runs":[{"text":"An index is a second structure, normally a B-tree, holding the values of one or more columns in sorted order, each with a pointer to where the full row lives. Because it is sorted, the database can binary search it. Four million rows becomes about 22 comparisons instead of four million."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE INDEX ON payments (student_id);\n\n-- Index Scan using payments_student_id_idx on payments\n--   (actual time=0.021..0.033 rows=6 loops=1)\n--   Execution Time: 0.08 ms"},{"t":"p","runs":[{"text":"612 ms to 0.08 ms. That is the whole trick, and it is a big trick."}]},{"t":"p","runs":[{"text":"Indexes are not free. Every INSERT, UPDATE and DELETE must update every index on the table. They take disk space. A table with twelve indexes that is written constantly will be slow to write and its indexes will mostly go unused. Index the columns you filter and join on, not every column you have."}]},{"t":"h2","id":"when-the-index-sits-there-unused","runs":[{"text":"When the index sits there unused"}]},{"t":"p","runs":[{"text":"You hid the raw value behind a function.","b":true}]},{"t":"code","lang":"sql","label":"SQL","text":"WHERE date(created_at) = '2026-03-14'   -- index on created_at is useless\nWHERE lower(email) = 'ada@example.com'  -- index on email is useless"},{"t":"p","runs":[{"text":"The index stores "},{"text":"created_at","code":true},{"text":", not "},{"text":"date(created_at)","code":true},{"text":". To compare, the database must compute the function for every row first, which means reading every row. Rewrite as a range, or build an index on the expression itself:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WHERE created_at >= '2026-03-14' AND created_at < '2026-03-15'\nCREATE INDEX ON users (lower(email));"},{"t":"p","runs":[{"text":"A leading wildcard.","b":true},{"text":" "},{"text":"LIKE '%kumar'","code":true},{"text":" cannot use a B-tree, because a sorted list does not help you find things by their ending. "},{"text":"LIKE 'kumar%'","code":true},{"text":" can."}]},{"t":"p","runs":[{"text":"The filter matches most of the table.","b":true},{"text":" "},{"text":"WHERE is_active = true","code":true},{"text":" when 95% of rows are active. Following the index and then fetching 95% of the rows one at a time is slower than reading the table straight through. When the planner ignores your index here, it is right and you are wrong."}]},{"t":"p","runs":[{"text":"The table is small.","b":true},{"text":" Scanning 500 rows takes microseconds. No index will beat that."}]},{"t":"h2","id":"the-wins-that-are-usually-bigger-than-an-index","runs":[{"text":"The wins that are usually bigger than an index"}]},{"t":"p","runs":[{"text":"Return less.","b":true},{"text":" Add the missing filter. Add a LIMIT. A query that returns 400,000 rows to an application that displays 20 is slow because of the 399,980, not because of the plan."}]},{"t":"p","runs":[{"text":"Stop using ","b":true},{"text":"SELECT *","b":true,"code":true},{"text":" on wide tables. If one column holds a 40 KB JSON blob, selecting it for every row means moving hundreds of megabytes across the network to display a name and a date."}]},{"t":"p","runs":[{"text":"Do not run one query per row.","b":true},{"text":" An application that fetches 900 orders and then runs one query per order to get the customer has made 901 round trips. At 3 ms each that is nearly three seconds, and the database will report every individual query as fast. One join does it in 12 ms. This is the most common performance bug in real software and it never shows up as a slow query in the logs."}]},{"t":"h2","id":"reading-a-plan-without-being-an-expert","runs":[{"text":"Reading a plan without being an expert"}]},{"t":"p","runs":[{"text":"Find the node with the largest "},{"text":"actual time","code":true},{"text":". That is your problem, not the scary-looking one at the top. Then compare the estimated "},{"text":"rows=","code":true},{"text":" against the "},{"text":"actual rows=","code":true},{"text":". If the planner expected 3 rows and got 900,000, it chose its whole strategy on a bad guess: usually stale statistics (run "},{"text":"ANALYZE","code":true},{"text":") or a filter it cannot reason about, like a function on a column."}]}],"check":{"question":[{"text":"A table has an index on "},{"text":"created_at","code":true},{"text":", a timestamp. The query "},{"text":"WHERE date(created_at) = '2026-03-14'","code":true},{"text":" still takes eight seconds. Why is the index not helping?"}],"options":[{"text":[{"text":"The index has gone stale after so many inserts and needs to be rebuilt"}],"correct":false,"tempting":[{"text":"Assumes indexes drift out of date, when in fact they are maintained on every write."}]},{"text":[{"text":"The index stores the raw timestamps, so the database must compute date() on every row before it can compare anything"}],"correct":true,"tempting":null},{"text":[{"text":"A timestamp is being compared to a string, and the type mismatch prevents the index from being used"}],"correct":false,"tempting":[{"text":"Reaches for a type mismatch, a real cause of index problems elsewhere, when the literal here would be cast fine."}]},{"text":[{"text":"The planner decided the table was too small to be worth an index lookup"}],"correct":false,"tempting":[{"text":"Applies a genuine planner behaviour to a case where the table is large and the filter is highly selective."}]}],"order":[2,3,1,0],"why":null},"prev":{"slug":"leakage-in-a-join","title":"Leakage: when a feature knows the future","minutes":10},"next":{"slug":"create-table-and-constraints","title":"CREATE TABLE: a constraint is a test that runs on every write","minutes":9}}}