Summary for the Impatient
ExoBench now runs as a chatbot at exobench.ai — no install, no setup. Paste a slow SQL query and an AI agent calls ExoBench, which spins up a real PostgreSQL database at multiple scales (100K, 1M, 3M rows), runs EXPLAIN ANALYZE, and tries indexes until the plan stops improving. The lesson: query plans change with scale, so the "obvious" index can win at small scale and lose at large scale — in the demo, a partial index sped the query up at 100K rows but made it slower at 3M, while a covering index stayed 4–7x faster everywhere. A naive LLM guesses an index from the SQL; ExoBench measures it at production scale.
Nobody Follows the Query Planner Plot
There's a Christopher Nolan movie rule: nobody really follows the plot. You nod along for the whole film and agree that some kind of ineffable intelligence strung together the scenes. Databases run on the same rule.
Nobody really understands how a query plan scales. We poke it with indexes until it gets fast.
Say it at a standup and watch every senior engineer quietly nod. The planner is a box that changes its mind as your tables grow and the workflow is: add an index, run it, check if the number dropped, and repeat until you get something working. The problem is that you cannot poke it at production scale so you play around with it on your laptop, double check it doesn't break in staging and quietly ship it, hoping and praying that production data lives in the same reality.
ExoBench does the poking, the scale, and even understands the plot well enough to give you back the right index. It now runs as a chatbot at exobench.ai. You paste in a slow query, an AI agent calls ExoBench, and ExoBench spins up a real database, multiple scales! At each scale, it runs EXPLAIN ANALYZE and tries one index after another until the plan stops improving. You watch, or you don't. The numbers below are the default demo, on PostgreSQL across 100K, 1M, and 3M rows.
The query is the kind we all have shipped:
SELECT id, customer_name, total, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;A pending-orders list, newest first. It looks innocent but gets slower as the table grows in a different way at each size. Nobody with sprint deliverables is going to track this in their head.
The Baseline is Three Plans
The agent measures the query as written, primary key index only, across three scales at once. Here's what shows up in the chatbot:
EXPLAIN ANALYZE SELECT id, customer_name, total, created_at FROM orders WHERE status = 'pending' ORDER BY created_at DESC;CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
customer_name text NOT NULL,
status text NOT NULL,
total numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (user_id, customer_name, status, total, created_at)
SELECT
(random()*100000)::bigint + 1,
'cust_' || (random()*100000)::int,
CASE
WHEN random() < 0.10 THEN 'pending'
WHEN random() < 0.55 THEN 'completed'
WHEN random() < 0.80 THEN 'shipped'
ELSE 'cancelled'
END,
(random()*1000)::numeric(10,2),
NOW() - (random() * 365 * interval '1 day')
FROM generate_series(1, ${SCALE}) i;
VACUUM ANALYZE;| Scale | Time | Plan | Compute |
|---|---|---|---|
| 100K | 10.2 ms | Baseline with no index on status or created_at. PostgreSQL runs a Seq Scan on orders, throws away 90,095 rows in the Filter, then quicksorts 9,905 surviving rows by created_at DESC in 925kB of work_mem. Execution Time 10.152 ms at 100,000 rows.QUERY PLAN
Sort (cost=2949.29..2974.42 rows=10053 width=32) (actual time=9.091..9.809 rows=9905 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 925kB
-> Seq Scan on orders (cost=0.00..2281.00 rows=10053 width=32) (actual time=0.010..7.183 rows=9905 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 90095
Planning Time: 0.061 ms
Execution Time: 10.152 ms | 3.09s |
| 1M | 62.9 ms | At 1,000,000 rows the unindexed ORDER BY goes parallel: Gather Merge over 2 launched workers, each running a Parallel Seq Scan that removes 300,010 rows by filter and a quicksort in about 3MB. Execution Time 62.944 ms.QUERY PLAN
Gather Merge (cost=19889.64..30095.42 rows=87472 width=32) (actual time=37.281..59.169 rows=99971 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=18889.62..18998.96 rows=43736 width=32) (actual time=34.695..38.876 rows=33324 loops=3)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 3287kB
Worker 0: Sort Method: quicksort Memory: 2512kB
Worker 1: Sort Method: quicksort Memory: 2420kB
-> Parallel Seq Scan on orders (cost=0.00..15518.33 rows=43736 width=32) (actual time=0.009..26.553 rows=33324 loops=3)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 300010
Planning Time: 0.086 ms
Execution Time: 62.944 ms | 4.83s |
| 3M | 185.3 ms | At 3,000,000 rows the per-worker sort no longer fits in work_mem, so quicksort becomes Sort Method external merge, spilling 5,032kB to disk under a 2-worker Gather Merge. Execution Time 185.338 ms, 18x the 100,000-row number.QUERY PLAN
Gather Merge (cost=61195.32..90500.07 rows=251166 width=32) (actual time=114.061..173.780 rows=300858 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=60195.30..60509.26 rows=125583 width=32) (actual time=110.212..121.060 rows=100286 loops=3)
Sort Key: created_at DESC
Sort Method: external merge Disk: 5032kB
Worker 0: Sort Method: external merge Disk: 4816kB
Worker 1: Sort Method: external merge Disk: 4856kB
-> Parallel Seq Scan on orders (cost=0.00..46553.00 rows=125583 width=32) (actual time=0.016..76.795 rows=100286 loops=3)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 899714
Planning Time: 0.066 ms
Execution Time: 185.338 ms | 13.34s |
Here comes the plan-reading. You can skim it. Seriously, skim it, I'll meet you at the number.
At 100K it's a sequential scan and an in-memory sort. At 1M Postgres decides to go parallel, because sure, why not? At 3M it stays parallel and the sort runs out of memory and spills to disk, which is the database sighing heavily. Three plans, one query. The planner changes its mind twice without telling you why.
Follow all of that? Doesn't matter. Here's the number: 10.2 ms, then 62.9 ms, then 185.3 ms. The line goes the wrong way. An LLM reading the SQL sees one query and one plan. ExoBench saw three, because it ran three.
If you want the why (nerds, welcome): ten percent of the rows are pending, the scan reads all of them anyway, and the sort is the expensive part. The fix keeps just the pending rows in date order. If you don't want the why: an index goes here. Onward.
The Obvious Index traps at Scale
The agent adds the index any of us would add: a partial index. The one you'd swear by.
CREATE INDEX idx_pending_created
ON orders (created_at DESC)
WHERE status = 'pending';Only here's what happens:
EXPLAIN ANALYZE SELECT id, customer_name, total, created_at FROM orders WHERE status = 'pending' ORDER BY created_at DESC;CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
customer_name text NOT NULL,
status text NOT NULL,
total numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (user_id, customer_name, status, total, created_at)
SELECT
(random()*100000)::bigint + 1,
'cust_' || (random()*100000)::int,
CASE
WHEN random() < 0.10 THEN 'pending'
WHEN random() < 0.55 THEN 'completed'
WHEN random() < 0.80 THEN 'shipped'
ELSE 'cancelled'
END,
(random()*1000)::numeric(10,2),
NOW() - (random() * 365 * interval '1 day')
FROM generate_series(1, ${SCALE}) i;
CREATE INDEX idx_pending_created ON orders (created_at DESC) WHERE status = 'pending';
VACUUM ANALYZE;| Scale | Time | Plan | Compute |
|---|---|---|---|
| 100K | 6.7 ms | Partial index idx_pending_created on (created_at DESC) WHERE status = 'pending'. At 100,000 rows the planner picks Bitmap Index Scan plus Bitmap Heap Scan over 1,031 heap blocks, still followed by a 935kB quicksort. Execution Time 6.677 ms against 10.152 ms for the Seq Scan baseline.QUERY PLAN
Sort (cost=1981.93..2006.63 rows=9877 width=32) (actual time=5.445..6.292 rows=10102 loops=1)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 935kB
-> Bitmap Heap Scan on orders (cost=172.14..1326.60 rows=9877 width=32) (actual time=0.469..3.404 rows=10102 loops=1)
Recheck Cond: (status = 'pending'::text)
Heap Blocks: exact=1031
-> Bitmap Index Scan on idx_pending_created (cost=0.00..169.67 rows=9877 width=0) (actual time=0.372..0.372 rows=10102 loops=1)
Planning Time: 0.083 ms
Execution Time: 6.677 ms | 0.91s |
| 1M | 83 ms | The trap: a bitmap scan returns rows in heap order, not index order, so ORDER BY created_at DESC still needs a Sort. At 1,000,000 rows that sort spills, Sort Method external merge Disk 4,880kB over 10,309 heap blocks. Execution Time 82.992 ms, slower than the 62.944 ms baseline.QUERY PLAN
Sort (cost=23783.79..24031.87 rows=99233 width=32) (actual time=68.744..78.463 rows=99817 loops=1)
Sort Key: created_at DESC
Sort Method: external merge Disk: 4880kB
-> Bitmap Heap Scan on orders (cost=1621.27..13171.68 rows=99233 width=32) (actual time=6.435..34.971 rows=99817 loops=1)
Recheck Cond: (status = 'pending'::text)
Heap Blocks: exact=10309
-> Bitmap Index Scan on idx_pending_created (cost=0.00..1596.46 rows=99233 width=0) (actual time=5.245..5.246 rows=99817 loops=1)
Planning Time: 0.086 ms
Execution Time: 82.992 ms | 4.76s |
| 3M | 245 ms | At 3,000,000 rows the partial index costs 244.986 ms against 185.338 ms for having no index at all. Bitmap Heap Scan touches 30,927 heap blocks, the sort spills 14,656kB to disk, and the single-process bitmap plan gives up the 2 parallel workers the Seq Scan plan used.QUERY PLAN
Sort (cost=72449.80..73171.55 rows=288700 width=32) (actual time=195.434..232.272 rows=300203 loops=1)
Sort Key: created_at DESC
Sort Method: external merge Disk: 14656kB
-> Bitmap Heap Scan on orders (cost=4820.10..39356.85 rows=288700 width=32) (actual time=20.224..104.989 rows=300203 loops=1)
Recheck Cond: (status = 'pending'::text)
Heap Blocks: exact=30927
-> Bitmap Index Scan on idx_pending_created (cost=0.00..4747.92 rows=288700 width=0) (actual time=15.301..15.301 rows=300203 loops=1)
Planning Time: 0.079 ms
Execution Time: 244.986 ms | 13.59s |
At 100K it worked. 10.2 down to 6.7. If your staging box holds a hundred thousand rows, you ship this, close the ticket, and call it a day.
At 3M it made things worse. 245.0 ms, slower than the 185.3 ms you started with. The obvious index, the one we were all so sure about, loses to doing nothing.
Cool. Cool cool cool.
The skippable reason: the planner picked a Bitmap Index Scan, which throws away the ordering the index existed to provide, so it sorts anyway and spills anyway. The unskippable reason it matters: you would have shipped this. It looked great at staging-scale. Nobody sees the flip coming, because the planner is ineffable... except for ExoBench.
Presto! The plan goes Flat
Now here's were ExoBench starts earning its keep. It feeds real stats back to your agent, the agent shrugs and keeps thinking. Next thing it tries is making the index covering, so the database never touches the table.
CREATE INDEX idx_pending_cov
ON orders (created_at DESC)
INCLUDE (id, customer_name, total)
WHERE status = 'pending';Here's what happens:
EXPLAIN ANALYZE SELECT id, customer_name, total, created_at FROM orders WHERE status = 'pending' ORDER BY created_at DESC;CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
customer_name text NOT NULL,
status text NOT NULL,
total numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (user_id, customer_name, status, total, created_at)
SELECT
(random()*100000)::bigint + 1,
'cust_' || (random()*100000)::int,
CASE
WHEN random() < 0.10 THEN 'pending'
WHEN random() < 0.55 THEN 'completed'
WHEN random() < 0.80 THEN 'shipped'
ELSE 'cancelled'
END,
(random()*1000)::numeric(10,2),
NOW() - (random() * 365 * interval '1 day')
FROM generate_series(1, ${SCALE}) i;
CREATE INDEX idx_pending_cov ON orders (created_at DESC) INCLUDE (id, customer_name, total) WHERE status = 'pending';
VACUUM ANALYZE;| Scale | Time | Plan | Compute |
|---|---|---|---|
| 100K | 1.5 ms | Covering partial index idx_pending_cov on (created_at DESC) INCLUDE (id, customer_name, total) WHERE status = 'pending'. The plan collapses to a single Index Only Scan with Heap Fetches 0 and no Sort node at all. Execution Time 1.483 ms at 100,000 rows.QUERY PLAN Index Only Scan using idx_pending_cov on orders (cost=0.29..440.34 rows=9870 width=32) (actual time=0.009..1.148 rows=9996 loops=1) Heap Fetches: 0 Planning Time: 0.032 ms Execution Time: 1.483 ms | 0.77s |
| 1M | 14.9 ms | Index Only Scan with Heap Fetches 0 at 1,000,000 rows, Execution Time 14.864 ms. The index already stores the rows in created_at DESC order and carries every selected column, so PostgreSQL skips both the heap and the sort.QUERY PLAN Index Only Scan using idx_pending_cov on orders (cost=0.42..4308.42 rows=96267 width=32) (actual time=0.020..11.518 rows=100196 loops=1) Heap Fetches: 0 Planning Time: 0.068 ms Execution Time: 14.864 ms | 4.33s |
| 3M | 44.8 ms | At 3,000,000 rows the covering index holds 44.760 ms against 185.338 ms with no index and 244.986 ms with the plain partial index. Still one Index Only Scan, Heap Fetches 0, no external merge, no parallel workers needed.QUERY PLAN Index Only Scan using idx_pending_cov on orders (cost=0.42..13240.92 rows=312300 width=32) (actual time=0.023..34.749 rows=300067 loops=1) Heap Fetches: 0 Planning Time: 0.068 ms Execution Time: 44.760 ms | 12.74s |
Index Only Scan at every scale. 1.5 ms, 14.9, 44.8. Heap Fetches: 0, no sort, no spill, the same flat green plan whether you hold a hundred thousand rows or three million.
Why does covering the index fix it? Skim away: the database walks the index in order and reads the selected columns straight out of it, so nothing is left to sort and nothing is left to fetch. Or the version you came for: it's fast now. You're done.
The Plot Synopsis
One query. Three index candidates. Three scales. Nine benchmarks. Here's the entire movie, and you can nod along.
The final results, with the speedup over baseline:
| Scale | Baseline (PK only) | Partial index | Covering index |
|---|---|---|---|
| 100K | 10.2 ms | 6.7 ms | 1.5 ms (6.8x) |
| 1M | 62.9 ms | 83.0 ms | 14.9 ms (4.2x) |
| 3M | 185.3 ms | 245.0 ms | 44.8 ms (4.1x) |
The winning index, no change to your query:
CREATE INDEX idx_pending_cov
ON orders (created_at DESC)
INCLUDE (id, customer_name, total)
WHERE status = 'pending';The LLM gets the Plot Wrong
A naive LLM would read your SQL and recommend an index with total confidence and it would recommend the partial one, the obvious one. It's the same one I'd have grabbed, and stopped there. It's the guy at your party explaining the Nolan plot in full detail getting it completely backwards because he only knows anything about it from a reddit post.
ExoBench watched the movie three times over, watched the Bitmap Index Scan drop the ordering, watched the sort spill, found the trap, and landed on the index whose plan stays flat.
An LLM guesses. ExoBench measures.
The Limits (read this one)
Everything else here is skippable. The limits are not, because here's where I tell you what ExoBench won't do.
The data is synthetic. ExoBench generated the pending/shipped/delivered split the demo describes, and a benchmark is only as honest as the distribution you give it. The agent guesses your schema when you don't hand it one, and it guesses wrong sometimes, so double check what it used! It benchmarks the query you give it, so a fast version of a bad query is still a bad query. Two runs can take different routes and both be fine. ExoBench leaves your infrastructure alone, so connection pools, memory pressure, and network latency stay your problem. The instances cap at a handful of scales and a few million rows each.
You still have to verify before you ship. The difference is you now have a number at production scale to verify against, in minutes, while sipping coffee.
Run your slowest query
Postgres users, your candidate is one query away:
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;Top row is the one. Go to exobench.ai, watch the demo poke this query until it's fast, then paste your own into the same box. Sign in with GitHub to run yours, one click, the same identity the connector uses. No install, no setup.
You don't have to understand how your query plan scales. ExoBench makes it fast. You can skim the rest.