Index advisor
An index advisor you can argue with
PostgreSQL has no index advisor of its own, so every recommendation anyone acts on came from somewhere. This page is about where ours comes from, and what it hands over alongside the SQL.
Producing the list is the easy half
Any competent DBA can produce a list of index candidates before lunch. One pass over the scan counters names the indexes nothing has read. One pass over the statement statistics, and a few plans, names the filters that wanted one. Producing the list has never been the difficulty. Deciding which lines on it are true is the difficulty, and that part does not get quicker with practice.
The reasons are all mundane. Statistics are kept per node, so an index untouched on the writer may be the only thing holding up a reporting replica. Counters are cumulative since somebody last reset them, and nobody remembers when that was. A job that runs at quarter end looks identical to a dead index for eleven months of the year. An index may exist to enforce a constraint rather than to serve a query at all. Each of those is a five-minute check on one database. None of them is a five-minute check on two hundred.
So the list gets produced, circulated, doubted, and quietly dropped. The same spreadsheet gets regenerated the following quarter by somebody who was not there the first time.
What an index advisor has to get right
Four tests. They are worth applying to any tool that offers to tell you what to index, including this one.
It reads every node
A cluster is not its primary. Counters drawn from one node describe one node, and the replica nobody logs into is exactly where the exception lives.
It prices the write side
An index is a standing charge on every insert and on every update that touches its columns. Advice that shows only the read it accelerates is half an argument.
It shows the plan it reasons from
The plan before, the plan after, and the statements that moved between them. A recommendation you cannot disagree with is not a recommendation.
It does not decide for itself
The advisor proposes. What decides is a rule an operator wrote down, not a confidence score the advisor assigned itself.
How DBExplore answers them
Before a statistic is read from anything, the shape of the cluster is worked out: which nodes exist, which of them takes writes, what sits in front of them, which extensions are installed and which are not. That is topology discovery, and index advice is drawn against the shape rather than against a connection string. It is what makes “unread everywhere it exists” a sentence the tool is entitled to say.
Scan counters are then collected from every node and aged against that node’s own last statistics reset, so a zero arrives with the window it was measured over attached to it. A drop is proposed only where the index is unread on all of them and is not enforcing a constraint or standing in as the replica identity. Where the advice is to build rather than to drop, the candidate is evaluated in a what-if pass and reported with the plan on both sides of it, the write cost it adds, and the space it wants.
Within a cluster the candidates come back ordered by return on investment rather than by size or by scan count, because the largest index on the list is rarely the one worth building first. The wider advisor — query rewrites, schema safety, the evidence model all three share — is on the platform tour, and what it looks like from a DBA’s side of the desk is on the page for DBAs.
None of that is an instruction to a database. A proposal reaches a cluster only through the autonomy ladder, and every tenant starts on its first rung, where the advisor watches and writes nothing at all. Moving index builds up a rung is something you do after watching the advice be right, and the fail-closed policy gate refuses anything outside what you wrote down. Approved or refused, the decision is appended to a signed, tamper-evident ledger you can verify without asking us; security and trust sets out how. The same proposal is readable by your own assistants over the MCP interface, and an assistant that asks to apply one meets the gate a person meets.
What arrives with a recommendation
Not a score and a statement. The observation behind it, the cost on the other side of the trade, and the approval it still needs.
proposal CREATE INDEX CONCURRENTLY ON orders (tenant_id, status) evidence 4 statements · plan captured either side of the candidate read filter discards most of what it reads · seq scan on orders write +2.4% on INSERT · ~1.2 GB · HOT updates 71% → 58% nodes unread on writer and both readers since last stats reset rung recommend · one approval required before anything runs
The row that gets argued about most is the write line. An index on a frequently updated column stops a share of updates qualifying for the cheap in-page path, and the cost of that lands on every write for as long as the index exists. A tool that leaves it out is not being optimistic, it is being incomplete.
Do it by hand first
None of this is secret, and you should not need anybody’s product to find your first bad index. Unused and missing indexes carries the queries we would run against a cluster we had never seen before, with the caveats that make each list wrong, and query plan regression covers reading the plan that comes after. Run them.
If the answer takes an afternoon and holds for a quarter, you do not have a fleet problem yet, and we would rather say that here than on a call. It is when the answers stop agreeing with each other across forty clusters, and the afternoon becomes a week somebody has to find, that a tool stops being an indulgence.
Point it at the fleet you cannot hand-check.
A pilot starts on the first rung, where the advisor only watches. You see the index advice against your own clusters before anything is allowed to act on it.