Guide · Reading
Which indexes are missing?
Ayushman ·
7 min read
Checked on 0.6.1
Open the index advisor when the database as a whole is slow and no one query owns the problem, or when a table carries indexes nobody can explain. It reads what the server has already run and ranks indexes to add and to drop. For one slow query, Will this index help? is quicker. For one index in question, read What an index page tells you.
The symptom
The web-orders report from Why is this query slow? takes 4,581 ms and reads all 12,000,000 rows of commerce.orders. billing.invoices spends 656 MB of its 2.3 GB on indexes, and invoices_customer_id_idx has not been scanned since the statistics reset. The question is which change saves the most time across everything the server runs, and what it costs the writes.
Where the suggestions come from
Right-click a PostgreSQL connection in the sidebar and choose Index advisor, or run Index advisor: <connection> from the command palette. Advise this table on a table's indexes page opens it filtered to that table. Workload impact beside a Plan tab Test result brings that index along as a row tagged plan.
The tab runs when it opens and again on Refresh, on a read-only session of its own. It reads the top 50 statements of the current database in pg_stat_statements by total time, skipping catalog reads and Seqora's own queries, and every index with its scans since the statistics reset.
From each statement it takes the columns compared with =, joined on, compared as a range and sorted on. A candidate puts the equality columns first, then one range or sort column, three keys at most. Seqora plans each statement with EXPLAIN, creates the candidate as a hypopg hypothetical index, and plans again. Statements with $1 in them plan as GENERIC_PLAN on PostgreSQL 16 and later, and with values sampled from pg_stats before that. Nothing runs and nothing is built.
The estimate is the recorded mean time scaled by the change in plan cost. A 4,581 ms statement whose cost falls by 90% is estimated at about 458 ms. Every predicted number carries est. for that reason.
An add is listed when it cuts some statement by 10%, saves at least 0.5% of all recorded time, and slows no statement by more than 10%. A three-key index must beat its two-key prefix by another 10%. At most five adds are listed, two per table. A run stops at 30 seconds or 200 candidates and says partial · time budget or partial · candidate cap.
The ranked list
The strip reads recorded time now and with every ranked change. The pane lists the statements the selected row changes.
The toolbar names the sources: pg_stat_statements · since reset with the days, the hypopg version, and primary only or primary + 1 replica. The strip reads Workload, the recorded total, →, the total with every ranked change, est., and how many are ranked.
Each row is a glyph, the index as table and keys, a reason line, and four numbers:
| Glyph | Row | Reason line |
|---|---|---|
+ |
add | 2 statements, or 2 statements · replaces <index> |
= |
duplicate | duplicate of <index> |
⊂ |
left prefix of a longer index | prefix of <index> |
∅ |
unused | 0 scans with the window, or too young · 9 d of 60 d |
workload is the share of recorded time the change saves, negative being faster. size is estimated for an add. writes is the table's writes per day, + for an add because each of them now maintains one more index, or HOT lost when a recorded UPDATE sets a column the index holds (Reading the stats explains HOT). kind is add, drop or skip.
On the sample, the web-orders report gives orders (channel, placed_at): channel is its equality column and placed_at its range. The drop is invoices (customer_id), prefix of invoices_customer_created_idx, −96 MB.
Select a row for its evidence. Statements changed lists each statement as q1, q2, with its mean struck through, the estimate, its calls and the time saved. Plan · q1 strikes the nodes that go and shows their replacements, and SQL holds the statement. Candidates that failed a gate fold under Tested, no gain with the reason, such as slows a statement or a shorter prefix does as well.
On Free the list holds the duplicate, prefix and unused drops, and a locked Ranked adds row stands for the rest. Ranked adds, the Matrix, the set and Build… are Pro.
One table as a matrix
A column with no filled cell and a + in every write row is paid for on every insert and read by nothing.
Matrix draws one table's statements against its indexes, existing first, then the ranked adds as dashed columns. Filled is uses now. An outline is could use: the index leads with a column the statement filters, joins or sorts on. Accent is would use, and + is upkeep on write. Click a dashed header to switch that index off, and the statements that relied on it fall back to today's plan.
Choosing a set
Tick rows. The footer, Set · 1 add · 1 drop, puts every ticked change in place at once and plans the touched tables again.
It shows workload and where it started from, disk, indexes per orders insert, and HOT updates as kept or lost. A statement more than 1.5× slower is named, as q3 slower, and Make migration stays off while one is. A clean set reads none slower. Make migration saves one _index_advisor.sql file, adds before drops, each statement on its own with no BEGIN.
Building it
Every statement the advisor writes uses CONCURRENTLY. A plain CREATE INDEX blocks writes to commerce.orders until it finishes, minutes on 12,000,000 rows. CONCURRENTLY lets writes continue, scans the table twice, waits for open transactions, and cannot run in a transaction block. A failed build leaves an invalid index that still costs writes.
Build… opens Build index with the SQL and CONCURRENTLY · own connection · no transaction. On production, type the database name first. Seqora runs the one statement on its own connection and waits at most 10 seconds for a lock. A failure reads Invalid index left with a DROP to copy.
The index then joins Built under the list. After a day and 1,000 new calls, Seqora measures the mean: verified within 1.25× of the estimate, miss otherwise, collecting until then.
Without the extensions
When either extension is missing, a setup card opens with the fix to copy: CREATE EXTENSION pg_stat_statements;, shared_preload_libraries = 'pg_stat_statements' when the library isn't loaded, or CREATE EXTENSION hypopg;. Seqora never runs them, and lists catalog drops only until both exist. An unused index waits for 30 days of statistics with replicas linked under Replica stats, 60 without, because a replica may read what the primary never does.
Caveats
- Every index costs writes. The
writesnumber on an add is how many rows a day will write one more index entry. The ranking charges recorded writes for it, but not the build or the disk. pg_stat_statements_reset()empties the workload andpg_stat_reset()the scan counts. An index a monthly job uses looks unused until the job runs.- The advisor sees only what the server recorded. With
pg_stat_statements.trackattop, the default, the foreign-key checks inside aDELETEare not recorded, so the missingbilling.payments (invoice_id)index from Why does this DELETE take three minutes? never appears. The database brief flags it asFK without index. - Estimates follow the statistics. Run
ANALYZEon a stale table first. - Partial, GIN, GiST and
INCLUDEcandidates are not generated. - MySQL and MariaDB get drops only, made
INVISIBLEorIGNOREDfirst. SQL Server gets drops and its missing-index views' adds, markeduntested. None has the Matrix, the set orBuild…, and SQLite has no advisor. See MySQL, MariaDB and SQLite.