Guide · Reading

What an index page tells you

Ayushman · 2026-10-17

Open an index page when a table has more indexes than you can explain, or when someone asks whether one can go. If the question is whether a new index would help one slow query, Will this index help? is the page. This one reads an index that already exists.

The symptom

billing.invoices is 2.3 GB on disk and 656 MB of that is indexes. Someone asks whether invoices_customer_id_idx can go. The table has 8,100,000 rows, nobody remembers who added the index, and DROP INDEX on a guess is how a quarterly report gets slow in January.

The index page of billing.invoices_customer_created_idx, a B-tree on (customer_id, created_at DESC) INCLUDE (status), with the one index that overlaps it.

What an index is

An index is a copy of a few columns of a table, kept sorted, with a pointer from each entry back to its row. This one holds every customer_id and created_at pair in invoices sorted by customer, so Postgres finds customer 1042's invoices by walking down a tree instead of reading 8,100,000 rows.

The header gives the size, 388 MB, and the usage, used 2.8M times in 55 days, last 2 hours ago. Everything from Key down is Pro. On Free the header keeps only the kind, the table and the engine, and the page stops after Definition with the list of what Pro adds over a blurred silhouette (Object pages shows the gate).

Key

The signature reads (customer_id asc, created_at desc) include status, above the first eight entries of the index, grouped by customer_id. How the entries are ordered reads the sample for you: to find customer_id = 1042, Postgres jumps straight to that group and reads down. Entries in a group are already in created_at order, so ORDER BY created_at with a LIMIT stops early.

The first column decides where an entry sits. The second only orders entries within one value of the first, so a filter on created_at alone gets nothing here. INCLUDE (status) copies status into each entry without making it part of the order: a query that needs only status can skip the table, but nothing can search by it.

Queries it can answer

One row per query shape, with the verdict the page prints:

Shape	Verdict
`WHERE customer_id = $1`	`Can use it`
`WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20`	`Can use it`, no sort step
`SELECT status … WHERE customer_id = $1`	`Can use it`
`WHERE created_at > $1`	`Can't use it`, `created_at` is second
`WHERE status = $1`	`Can't use it`, `status` is only carried

With and without this index

Compare beside a shape plans the query with and without this index, hiding it from the planner in your session through hypopg. For the ORDER BY shape the headline is About 51× cheaper with it: planner cost 6.2 with the index, 317 without. Both are estimates, not timings.

Where the space goes

A treemap draws the table's rows and each index with area equal to size on disk. invoices and its indexes take 2.3 GB, and this index is 388 MB of it, 17%.

Each write pays for it: a new row is written to the table and to up to 3 indexes, this one included. 64% of updates on invoices are done in place, so no index entry moves. An update that changes customer_id, created_at or status never is, because this index stores them.

Wasted space estimates what a rebuild would reclaim: about 31 MB here, 8% of the index, so No rebuild needed.

One other index overlaps this one

The table lists each sibling that shares this index's first column. invoices_customer_id_idx is 96 MB, has 0 scans and was last used never, counting since the statistics were reset on 2026-08-23. The page marks it covered by this index, because every lookup it answers this one answers too: dropping it frees 96 MB and one write per insert, and the foreign key on customer_id stays indexed by this one.

An index nobody uses

That is the common problem: an index every insert pays for and no query reads. idx_scan and last_idx_scan count since the last statistics reset, and the footer says when that was, so check the window first. A month is not long enough for an index that serves a quarterly report. Seqora won't call an index unused until seven days have passed, and never flags one under 1 MB or one that backs a constraint.

Then Review drop opens a console with the statement and the caveats as comments:

-- invoices_customer_id_idx is covered by invoices_customer_created_idx.
-- Scan counts are per server: check read replicas before dropping.
DROP INDEX CONCURRENTLY billing.invoices_customer_id_idx;

All properties and DDL fold out under the sections. The chips say Valid and ready, or Not usable yet after a CREATE INDEX CONCURRENTLY that failed, and an invalid index gets a red verdict.

Caveats

Usage counts are per server and per statistics reset. An index the primary never reads may be what keeps a reporting replica fast.
Indexes that back a primary key, unique or exclusion constraint are never called redundant, because they enforce something.
On MySQL and MariaDB the column is Reads, not Scans, and the page offers INVISIBLE as a reversible way to try life without an index. SQLite counts no index usage. The sizes here come from the 12-million-row copy of the sample.