Add an index vs full table scan

The decision

A full table scan reads every row of a table to find the rows that match. An index is a separate, sorted structure that lets the database jump to the matching rows. The usual type is a B-tree, a balanced tree of pages. An index makes some reads much faster. In return, it makes every write slower and uses disk and memory.

Choose an index when

• A query returns a small share of the table, such as one customer's orders out of millions.
• The same query runs often, or must be fast, for example on a screen the user is waiting for.
• The query sorts or limits on a column, such as "newest 10 orders". A composite index, one index on both the filter column and the sort column, can avoid the sort.
• Writes to the table are much fewer than reads.

Choose the full scan when

• The query needs most of the table, for example a report over 30% of the rows. The database will choose a scan itself, and that is correct.
• The table is tiny, a few hundred rows. A scan is as fast as an index.
• The table takes very heavy writes and the query runs rarely, such as a monthly report.

The numbers that change the answer

Worked example at Indian scale. The orders table of a shopping app. Assumptions: 40,000,000 orders of 200 bytes and 2,000,000 customers. A page is 8,192 bytes. A scan reads 1 GB per second. A random page read takes 0.1 ms (a fast local SSD; network disks are slower, for example AWS says its fastest type, io2 Block Express, averages under 0.5 ms; at 1 ms per read the index plan below would still take 24 ms against 8 seconds). At peak, 120 new orders arrive per second.

Assumption: a B-tree page holds 300 entries. Three levels reach 300 x 300 x 300 = 27,000,000 rows, which is too few, so 4 levels are needed. The first index is about 8,200 / 2.4 = 3,400 times faster.

When the scan wins. A scan reads one page in 8,192 bytes / 1 GB per second = 0.008 ms. A random read costs 0.1 ms, which is 12.5 times more. In the worst case, each row sits on its own page. The scan costs 1,000,000 x 0.008 ms = about 8,000 ms. An index costs 0.1 ms per row. Break-even: 8,000 / 0.1 = 80,000 rows, which is 0.2% of the table. This is a simple model, and the real planner has cheaper ways to read many rows. But the idea holds: an index pays only for a small share of rows.

The write price. Each new row changes one table page and one page in every index. At 120 orders per second:

• 1 index: 120 x (1 + 1) = 240 page changes per second.
• 6 indexes: 120 x (1 + 6) = 840 page changes per second. That is 3.5 times more.

Memory. Assumption: a page holds 300 entries, so each entry takes about 27 bytes of page space (8,192 / 300, which includes the key, the row pointer and the page's own overhead). One index is 40,000,000 x 27 = 1.08 GB. Six indexes are 6.5 GB.

What breaks with each choice

• Index: every write pays. Postgres says an index adds overhead to data changes and that seldom-used indexes should be removed. A plain CREATE INDEX blocks writes while it builds. CREATE INDEX CONCURRENTLY does not block writes, but it takes longer and can fail and leave an invalid index that you must drop. Column order matters: an index on (customer_id, created_at) cannot serve well a query that filters only on created_at.
• Full scan: time grows with the table. Ten times more rows takes ten times longer. A scan also fills the memory cache with pages that nobody needs again, which slows other queries.

What it costs

• An index costs disk, memory and slower writes, as shown above.
• Adding one is cheap to do and cheap to undo. You can drop an index in seconds. On a busy table, build it with the concurrent option and watch the load.
• Finding the right index costs time: read the plan, the way the database chose to run the query, with EXPLAIN, and test on a copy.

How each level answers

SDE-1: An index is a sorted structure that finds rows fast. A full scan reads every row.

SDE-2: I add an index on customer_id for "my orders", because it returns 20 rows out of 40,000,000. I skip it for a tiny table or a report that needs most of the rows.

Senior: The scan reads 1,000,000 pages and takes about 8 seconds. A composite index on (customer_id, created_at) takes 14 reads, 1.4 ms. Six indexes would make writes 3.5 times heavier at 120 orders per second. The risk is a lock during the build, so I use the concurrent option. The fallback is to drop the index.

Staff: I ask which screens the business cares about and how much latency they need. I keep a short list of agreed queries and one index for each, and I review unused indexes every quarter. A team owns the schema changes. I check the choice with query time at the 99th percentile, write latency, and the number of indexes that no plan uses.

Say it in the interview

I would add a composite index on customer ID and created time. Then "my last 10 orders" takes 14 page reads, not a scan of 1,000,000 pages. Each index makes writes heavier, so I add one only for a real query and check the plan with EXPLAIN.

Common mistakes

• Adding an index on every column. Six indexes make writes 3.5 times heavier than one index in our example.
• Expecting an index to help a query that returns most of the table. The planner will usually ignore it.
• Wrong column order in a composite index. Put the equality column first and the sort column last.
• Guessing instead of reading the plan with EXPLAIN.

Sources

• Postgres, introduction to indexes (indexes add overhead to data changes; seldom-used indexes should be removed): https://www.postgresql.org/docs/current/indexes-intro.html (opens in a new tab) (checked 26 Oct 2026).
• Postgres, CREATE INDEX (a normal build locks out writes; CONCURRENTLY does not, takes longer and can leave an invalid index): https://www.postgresql.org/docs/current/sql-createindex.html (opens in a new tab) (checked 26 Oct 2026).
• Table size, speeds and the 300 entries per page are assumptions made for teaching. The break-even is a simple model of our own. The disk speed claim: AWS EBS volume types page, "io2 Block Express volumes are designed to deliver an average latency of under 500 microseconds for 16KiB I/O operations": https://docs.aws.amazon.com/ebs/latest/userguide/ebs-volume-types.html (opens in a new tab) (checked 27 Oct 2026). PostgreSQL's planner uses random_page_cost 4.0 against seq_page_cost 1.0 by default and says to lower it for cached data: https://www.postgresql.org/docs/current/runtime-config-query.html (opens in a new tab) (checked 27 Oct 2026).