What this simulates
Every index speeds up the reads it matches and slows down every write to that table — SQL Index Advisor says as much but stops short of putting a number on it, since it only analyzes one query in isolation. This tool puts both sides of that trade-off in the same units — row and B-tree node operations per day — so "is this index worth it" becomes a comparison instead of a guess.
The model, in full
Without an index, a query filtering on a non-indexed column has no choice but a full table scan: every row examined, cost proportional to the row count. With a B-tree index, the same lookup costs roughly the tree's height — see Understanding B-Trees for exactly why: at a realistic branching factor of 200 keys per node (one B-tree node per disk page), a million-row table needs a height of only log₂₀₀(1,000,000) ≈ 3, this tool's exact assumption reused here for consistency.
read work without index = reads/day × rows
read work with index = reads/day × log₂₀₀(rows)
read work saved = the differenceEvery index also has to be updated on every write that touches it — an INSERT, an UPDATE of an indexed column, or a DELETE — at roughly the same B-tree-descent cost as a lookup, since inserting into a B-tree costs the same order of work as searching it for where to insert:
write work before = writes/day × existing index count × log₂₀₀(rows)
write work after = writes/day × (existing index count + 1) × log₂₀₀(rows)
write work added = the differenceNet change is read work saved minus write work added — positive means the reads this index accelerates outweigh the writes it burdens, in raw operation-count terms.
What this deliberately doesn't model
This is an educational simulator, not a query planner — it works entirely from the numbers you provide, not from anything about your actual schema, data distribution, or hardware. It specifically doesn't know:
- Real latency. This compares operation counts, not milliseconds — a row scan and a B-tree node visit aren't the same number of nanoseconds in reality, and neither is constant across storage engines or whether data is cached in memory.
- Selectivity. A full table scan for a condition matching 90% of rows and one matching 0.01% of rows cost the same here (every row still gets examined) — real databases sometimes skip an index entirely for a low-selectivity condition because a scan is genuinely cheaper, something SQL Explain Helper reveals from a real execution plan and this simulator can't see.
- Storage cost. An index also consumes disk space roughly proportional to the indexed columns × row count — real, but left out of this operation-count model entirely.
- Caching. A frequently-scanned table's pages often stay resident in memory, which narrows the real-world gap this model shows as enormous — the model assumes every operation actually touches storage.
Treat the verdict here as a first-pass, order-of-magnitude signal for whether a trade-off is even close — not a substitute for testing against real data with SQL Explain Helper.
FAQ
Why does "existing indexes" matter if I'm only adding one?
Because the marginal write cost of one more index depends on how many the table already pays for on every write — a table with zero indexes going to one behaves very differently under load than one going from nine indexes to ten, even though both are "adding one index." This also means a primary key or unique constraint counts as an existing index in most engines — don't forget to include it.
Why B-tree specifically, not a hash index?
B-tree is the default and by far the most common index type across PostgreSQL, MySQL (InnoDB), and SQL Server, and it's the type SQL Index Advisor generates — see Understanding B-Trees for the full mechanism and why it's the default choice.
Is anything I enter here sent anywhere?
No — this calculation runs entirely in your browser. Nothing here is ever sent to a server.