Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Indexes a wider one already covers · 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 a wider one already covers

A composite index on (a, b) can serve any query that a single-column index on (a) could — that is the leftmost-prefix rule. So whenever both exist, the narrow one is redundant and is costing disk and write time for nothing. Find every index whose key columns are a leading prefix of another index on the same table. Return both names, both column lists, and the disk the redundant one would release. Order by reclaimable_mb descending, then redundant_index.

Result columns · in this order

`redundant_index`	The index a wider one already covers.
`redundant_columns`	Its key columns.
`covered_by`	The wider index that covers it.
`covering_columns`	The wider index's key columns, starting with the redundant one's.
`reclaimable_mb`	Disk released by dropping the redundant index.

How to approach it

Self-join the catalogue on table_name and match key_columns with an anchored LIKE that requires a following column.

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.

redundant_index	redundant_columns	covered_by	covering_columns	reclaimable_mb
idx_items_order	order_id	idx_items_order_sku	order_id, sku	390
idx_orders_customer	customer_id	idx_orders_cust_created	customer_id, created_at	180
idx_customers_email	email	idx_customers_email_source	email, signup_source	52

3 rows — all rows shown.

Constraints

1key_columns holds the keys in index order, comma-separated. Order is everything: (customer_id, created_at) covers (customer_id) and does not cover (created_at).
2Match a leading prefix, not a substring. LIKE narrow || ', %' anchors at the start and requires a following column, so an index never matches itself and (sku) never matches (order_id, sku).
3An index must not pair with itself — exclude the equal-name case explicitly.
4Compare only within a table. Two tables can hold identically-named columns and neither covers the other.
5Order by reclaimable_mb descending, then redundant_index.

Worked example

idx_items_order is on order_id. idx_items_order_sku is on order_id, sku. Any query the narrow one can serve, the wide one serves too by reading only its first key column — so the narrow index is 390 MB doing nothing that is not already done. Three pairs qualify, releasing 390, 180 and 52 MB. Now look at what does not match. idx_items_sku is on sku, and idx_items_order_sku contains sku as its second key. A B-tree is ordered by the first column, then the second within it, so it cannot be seeked on sku alone. Matching a substring instead of a leading prefix would wrongly flag that index for deletion — which is why the pattern is anchored.

What this tests

The leftmost-prefix rule, and whether you can express 'is a leading prefix of' as a join condition without accidentally matching substrings.

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