Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Indexes that only cost writes · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Indexes that only cost writes

Inserts on this database have got slower over two years and nobody has removed an index in that time. Every write to a table has to maintain every index on that table, so an index nothing reads is a pure tax. Return the indexes scanned fewer than 100 times in the last 30 days, with the write volume they are riding on. Order by size_mb descending, then index_name.

Result columns · in this order

`index_name`	The index.
`table_name`	Table it sits on.
`key_columns`	Its key columns, in order.
`size_mb`	Disk it occupies.
`writes_last_30d`	Writes to the table in 30 days — every one maintained this index.

How to approach it

Filter on the scan count, report the write count beside it, and sort by size.

Sample input

index_catalogOne row per index, with its key columns in order, its size, and how often it was scanned and written to in the last 30 days.

index_name	table_name	key_columns	size_mb	scans_last_30d	writes_last_30d
orders_pkey	orders	order_id	210	4100000	1800000
idx_orders_customer	orders	customer_id	180	920000	1800000
idx_orders_status	orders	status	96	0	1800000
idx_orders_created	orders	created_at	175	340000	1800000
idx_orders_cust_created	orders	customer_id, created_at	260	510000	1800000
idx_orders_is_test	orders	is_test	88	0	1800000
idx_items_order	order_items	order_id	390	2600000	7200000
idx_items_order_sku	order_items	order_id, sku	610	1400000	7200000
idx_items_sku	order_items	sku	420	12	7200000
customers_pkey	customers	customer_id	44	880000	120000
idx_customers_email	customers	email	52	410000	120000
idx_customers_email_source	customers	email, signup_source	70	60000	120000
idx_customers_country	customers	country	18	0	120000

13 rows — scroll inside the table to see them all.

Expected output

The exact result your query should return for this input.

index_name	table_name	key_columns	size_mb	writes_last_30d
idx_items_sku	order_items	sku	420	7200000
idx_orders_status	orders	status	96	1800000
idx_orders_is_test	orders	is_test	88	1800000
idx_customers_country	customers	country	18	120000

4 rows — all rows shown.

Constraints

1The threshold is fewer than 100 scans in 30 days — not zero. An index used three times a month is almost certainly a report somebody could run differently.
2Report writes_last_30d beside each one. That number is the cost the index imposes and the argument for dropping it.
3Order by size descending: the biggest unread index is both the most disk reclaimed and usually the most write amplification.
4Do not drop anything on this evidence alone — the constraints below say why.
5Order by size_mb descending, then index_name.

Worked example

idx_items_sku was scanned 12 times in thirty days and sits on a table taking 7,200,000 writes in the same period. It is 420 MB of disk that every one of those writes has to maintain, in exchange for twelve lookups. Four indexes qualify. Three of them — on status, is_test and country — are the low-selectivity columns from the selectivity audit, which is not a coincidence: an index the planner will not choose is an index nothing scans. Two cautions before anybody writes a migration. A unique index enforces a constraint whether or not queries use it, so dropping it changes what the database permits. And a thirty-day window misses anything quarterly — the month-end index looks unused for twenty-nine days out of thirty.

What this tests

That an index has a running cost as well as a benefit, and that usage statistics are evidence rather than a verdict.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules