Most index advice is a hunch with a table name attached. A report is slow, somebody checks pg_stat_user_tables, finds a table with a lot of sequential scans, and concludes that it needs an index. Which column? Well. That part is usually left as an exercise for the reader, and the reader is usually the person on call.
The catalog is genuinely good at telling you something is wrong here. It is much worse at telling you what to build, because the information it keeps is about tables and indexes, not about the queries that touched them. pg_stat_user_tables knows a table was read end to end 129,408 times. It does not know that every one of those reads was throwing away rows on status = 'shipped', and it has no opinion at all about whether the right answer is a B-tree, a GIN index, or a rewrite.
The Index Advisor in the August 2026 release of the PostgreSQL Plug-in for Oracle Enterprise Manager is built around that gap. It runs three layers of detection, and each layer is a different kind of evidence: what the catalog can see on its own, what the planner says a candidate index would be worth, and what your workload actually filtered on. The second and third layers come from two optional PostgreSQL extensions, HypoPG and pg_qualstats, and they are the difference between a ranked list of suspicions and a ranked list of measurements.
This post has three parts: the catalog-native layer that works with nothing installed, the two extensions and exactly what each one adds, and why the plug-in shows their answers in two separate tables instead of merging them into one score.
Layer one: what the catalog already knows
The base layer runs on every monitored target with no extension installed at all, once per database on each collection, over PostgreSQL's own catalog and statistics views. It covers five categories:
| Category | What fires it | Severity |
|---|---|---|
| Missing | A table with at least 50 sequential scans, more sequential scans than index scans, at least 1,000 live rows, and an average of at least 100 rows read per scan | MEDIUM |
| Unused | A valid, non-primary-key, non-unique index with zero scans since the last statistics reset | LOW |
| Invalid | pg_index.indisvalid = false, typically a failed CREATE INDEX CONCURRENTLY. Maintained on every write, serves no query | HIGH |
| HOT-inhibiting | A valid non-primary-key index on a table with at least 50 updates whose HOT-update ratio is below 50% | MEDIUM |
| Consolidation | An index whose column list is a leading prefix of another index on the same table using the same access method | LOW |
Four of those five are about indexes that already exist, and they are the layer's strongest work. Invalid is close to a free win: a failed concurrent build leaves behind an index that costs you write throughput and returns nothing, and nothing in the console tells you it happened. Consolidation is the one people are most surprised by. Two years of well-meant index additions leave redundant prefixes behind, and each one is a write-time tax nobody chose to pay.
Every row carries its own evidence sentence, the number the rule fired on, and remediation SQL in CONCURRENTLY form. Findings from every database on the instance land in one table, told apart by the Database column.
Two of these deserve a second look before they reach a change ticket. The HOT-inhibiting drop is explicitly advisory: the index may still be serving reads, and only you know whether the write cost is worth paying. And Unused means unused since the last PostgreSQL statistics reset. After a reset, perfectly healthy indexes look idle until real traffic accumulates again. Check when statistics were last reset before you drop anything.
Then there is Missing, which is the honest weak spot. The rule identifies a table being read end to end, repeatedly, at volume. That is a real signal. But the catalog cannot tell you which column to index, so with no extension installed the recommendation is a CREATE INDEX CONCURRENTLY skeleton with a placeholder comment where the column list goes. So the catalog gets you a correctly identified problem and a deliberately unfinished answer. Finishing it is what the two extensions are for.
Layer two: HypoPG prices the candidate
HypoPG does one thing extremely well. It lets you create an index that does not exist, tell the planner about it, and ask what the plan would cost. Nothing is written to disk, no lock is taken, and the whole thing disappears when you reset the session. On a production system that distinction matters enormously: the alternative way to find out whether an index helps is to build it, and building a multi-gigabyte index to discover it was the wrong column is an expensive way to learn.
With hypopg installed in a database, every catalog-native Missing candidate in that database gets tested by simulation instead of being left as a hunch:
- The advisor picks the most selective indexable column on the candidate table that is not already the leading column of an existing index: highest distinct-value fraction, restricted to types that have a default B-tree operator class. A table with no simulatable column is skipped.
EXPLAIN (FORMAT JSON)on an equality lookup against that column captures the current plan's total cost. That is the Baseline Cost.- A hypothetical B-tree index is created with
hypopg_create_index(...), the query is re-planned, and the new estimate becomes the Hypothetical Cost. - Est. Speedup (x) is baseline divided by hypothetical, and the row records whether the planner actually chose the hypothetical index.
hypopg_reset()runs after every candidate, so no hypothetical index leaks into the next simulation.
Note the column labeled Planner Adopts. It reads 1 when the re-planned query actually used the hypothetical index and 0 when the planner looked at it and declined. A candidate with a high estimated speedup that the planner does not adopt is worth a much closer look before you build anything, because the two numbers are telling you different things.
Both measurements come from plain EXPLAIN (FORMAT JSON) with no ANALYZE. The synthetic lookup is planned and costed, never executed.
And treat the speedup as a prioritization signal rather than a promise. It is a ratio between two planner cost estimates, and cost estimates are a model of the work, not a stopwatch. Its job is to tell you which of four candidates deserves your attention first. In the screenshot above, bloat_churn at 36,962x and events at 1.67x is not a claim about wall-clock time; it is an unambiguous answer to "which one first."
There is one boundary worth stating plainly, because it explains the whole shape of the next section. The What-If simulation is B-tree only, and two separate things produce that. HypoPG models B-tree, hash, BRIN, and bloom, but not GIN or GIST, so containment and overlap predicates fall outside what a cost simulation here could price at all. Within the methods it does model, the plug-in picks one candidate column and simulates a B-tree for a column = value lookup, which is the general case. So for equality, simulation is the right tool. For a JSON containment predicate, simulation has nothing to say, and the answer has to come from somewhere else.
Layer three: pg_qualstats reports what your workload actually filtered
pg_qualstats, maintained by the powa-team, takes the opposite approach. Rather than modeling a hypothetical future, it records what already happened: it keeps statistics on the predicates found in WHERE and JOIN clauses across your workload, including how many rows each one threw away.
The critical detail is that it records how each predicate was evaluated. Its eval_type column distinguishes a predicate that ran as an index condition from one that ran as a post-scan filter. The plug-in reads only the filters: predicates your queries actually evaluated, that no index served. That is what makes this the sharpest missing-index evidence on the page. These are not simply the predicates your workload ran; they are the ones it ran the expensive way.
From there:
- Observed filter predicates are aggregated per table, column, and operator, with three evidence numbers: how many distinct plans the predicate appeared in, how many executions evaluated it, and how many rows it filtered away.
- The access method is inferred per operator, from PostgreSQL's own operator-family catalogs, in preference order B-tree, GIN, GIST, SP-GiST, BRIN, hash. This is the part cost simulation structurally cannot do. JSON and array containment resolve to GIN, range and geometry overlap to GIST, scalar equality to B-tree. For non-B-tree recommendations the correct operator class is resolved and written into the SQL.
- The existing-index check is access-method aware. A B-tree index already on a column does not suppress a needed GIN recommendation for a different operator on that same column. That is correct, and it is a mistake simpler advisors make constantly.
- Severity comes from filtered volume: 100,000 rows or more is HIGH, 1,000 or more is MEDIUM, below that LOW. Impact Rank orders by rows filtered, then executions.
Each row spells out its own case: the predicate, how many plans it appeared in, how many rows it discarded over how many evaluations, and the access method that follows from the operator. And unlike the catalog-native skeleton, the recommendation is a complete, runnable statement with the operator class filled in:
CREATE INDEX CONCURRENTLY ON xpgs_demo.events USING gin (payload jsonb_ops);One caveat to hold onto, and it rhymes with the statistics-reset trap in the catalog layer: pg_qualstats keeps its data in shared memory and does not persist it across a PostgreSQL restart. After a restart the predicate evidence rebuilds from zero, so a quiet Predicate-Stats table on a recently bounced server means "not enough workload observed yet," not "nothing to fix."
Why the two tables stay separate
The Index Advisor shows these as two side-by-side tables under Missing Index Recommendations, one headed Cost-simulated · HypoPG What-If and the other Predicate-observed · pg_qualstats, and does not merge them into a single blended score. Look at the two screenshots above and you can see why.
xpgs_demo.events, column payload, appears in both. HypoPG picked payload as the most selective indexable column, simulated a B-tree on it, and returned a shrug: 1.67x. pg_qualstats saw what the workload was actually doing to that column: a JSON containment predicate, @>, evaluated as a filter 16,839 times, and recommended GIN with jsonb_ops.
Neither layer is wrong. They answered different questions. A B-tree on payload really is worth about 1.67x for an equality lookup, and an equality lookup is not what anyone is running against that column. Blending those into one number would have produced a confident average that pointed at the wrong index.
So the split is the design. One table is planner cost simulation; the other is observed workload evidence; they are independent, and where both point at the same table, that agreement is the strongest signal on the page. The Top recommendation · Missing index banner is built on exactly that: it promotes a What-If candidate to the top of the page and, when the predicate-stats layer saw queries against the same table, annotates it with a pg_qualstats matches: N queries note, putting both lines of evidence on one row.
That banner is a starting point rather than a verdict, though. The full HypoPG What-If Simulation and Predicate-Stats Advisory sections below it are the audit trail, and on a page with several candidates they are worth reading rather than skipping, because the highest-impact row for your workload is not always the one promoted to the top.
Installing the two extensions
Both are per-database, which surprises people: CREATE EXTENSION in one database does not cover its neighbors. The plug-in probes for them per database on every collection, so a database with hypopg installed is simulated even when the one beside it is not.
# PGDG packages, matched to your server major version
sudo apt install postgresql-16-hypopg postgresql-16-pg-qualstats-- Run in each database you want covered
CREATE EXTENSION hypopg;
CREATE EXTENSION pg_qualstats;One operational difference matters for planning. hypopg needs no restart. It is a pure planner-side extension, so CREATE EXTENSION is the whole job. pg_qualstats collects into shared memory and must be listed in shared_preload_libraries, which means a restart. If you are working to a maintenance window, that is the item to schedule; install them together and you pay for one window rather than two.
Install both if you can. They are complementary rather than alternative, and the section above is the reason: without hypopg you lose the ability to rank candidates by projected impact, and without pg_qualstats you lose GIN and GIST recommendations entirely, because nothing else on the page can infer them.
The Monitoring Readiness page reports exactly where each target stands. Its "Index Advisor — Enhanced Recommendations" panel lists both extensions with a status chip and states the trade directly: catalog-native detection always works; these add What-If simulation and predicate-based recommendations.
When a finding becomes an alert
The part that makes this operational rather than a page somebody remembers to open: every finding also publishes as a standard Enterprise Manager metric on the target. That means editable collection schedules, thresholds you can tune per target or through a monitoring template, alert history, and routing through whatever notification connector you already have bound.
| Metric | Collected | Default Warning |
|---|---|---|
| Index Advisor | Every 30 minutes | Severity matches HIGH |
| Index Advisor What-If | Every 30 minutes | Estimated Speedup greater than 10 |
| Index Advisor (Predicate Stats) | Every 30 minutes | Severity matches HIGH |
The threshold on estimated speedup is the interesting one. A projected tenfold improvement is upside too large to leave sitting unread on a page nobody opened, so it raises an incident in the console your DBAs already watch. Alerts clear at the next collection after the finding resolves, which makes the clear event your verification that the change did what you expected.
Three importable monitoring templates apply a curated set in one step: ip_xpgs_production_critical enables all three collections on a 15-minute schedule with thresholds on, ip_xpgs_standard runs the catalog-native collection hourly with its threshold disabled so findings are collected and alerting stays opt-in, and ip_xpgs_starter is a seed to clone.
Is it safe to install HypoPG on a production database?+
HypoPG does not modify data, does not build anything on disk, and does not take locks. A hypothetical index exists in the planner's memory for the life of a session and vanishes on reset, so the resource cost is a small amount of memory during the simulation itself. What it does do is influence planning inside the session that created it, which is why the plug-in calls hypopg_reset() after every candidate, so no hypothetical index survives into the next simulation. The usual advice applies regardless: install it in a non-production environment first, because that is true of any extension.
Does pg_qualstats slow down my queries?+
It adds bookkeeping to record the predicates it sees, so the cost is not zero. Its defaults are already conservative, though, and it is worth knowing which way the knobs turn. pg_qualstats.sample_rate defaults to -1, meaning automatic, which resolves to 1 / max_connections: out of the box it samples a small fraction of queries rather than observing every one. So on a busy system the question is usually whether to raise the sample rate to gather evidence faster, not lower it to reduce overhead. pg_qualstats.max caps how many predicates and query texts are tracked, defaulting to 1,000, and pg_qualstats.track_constants, on by default, is the setting to disable first if you need to cut the number of entries considerably. The extension's own documentation covers all of them.
Why does the What-If simulation only ever recommend a B-tree?+
Two different limits get conflated here, so it is worth separating them. HypoPG models B-tree, hash, BRIN, and bloom, so the extension is not what confines this to B-tree. That part is the plug-in's own choice: it picks the most selective indexable column on the candidate table and simulates a B-tree for a column = value lookup, which is the general case for a missing-index candidate. What HypoPG genuinely cannot do is model GIN or GIST costs, so those recommendations arrive from the predicate-stats layer instead, inferred from the operators your workload actually used. This is precisely why installing only hypopg leaves a real gap: nothing else on the page can produce a GIN recommendation.
An index shows as Unused, but I'm certain it gets used. What's going on?+
Almost always the statistics reset. "Unused" means zero scans since the last PostgreSQL statistics reset, not zero scans ever, and a reset can come from an explicit pg_stat_reset(), a restore, or a maintenance script somebody wrote years ago. After one, every healthy index in the database reads as unused until real traffic accumulates again. Check when statistics were last reset before acting on anything in that category. There is a second case worth knowing: on a standby, index size reads 0, because the size lookup is skipped during recovery.
Do I need these extensions to get any value out of the Index Advisor?+
No. Every part of the page works without them: all five catalog-native categories, the KPI band, both recommendation banners, the Unused and Invalid table, impact ranking, and recommendation SQL. The extension-gated sections simply come up empty, with no error and nothing half-rendered. What you lose without them is precision on missing indexes specifically: you get a correctly identified table with a placeholder for the column list, rather than a complete statement backed by either a cost estimate or observed predicates. The extensions add precision rather than function.
Will the plug-in create or drop an index on its own?+
No. Nothing on this page executes without a person deciding that it should. Every finding produces a statement in a dialog with a Copy to Clipboard button, and where the offending index is known the statement is exact and schema-qualified. In this release you take that statement to your own tooling and run it there. The rule behind it does not move: the advisor recommends, and the execution decision stays with the DBA and whatever change process you already run.
How current are the findings? Does the page poll?+
It collects once when the page loads, and there is no background polling. The detection queries are heavier than an ordinary metric read and findings change slowly, so reload the page to refresh it. The three metrics behind the page are separate from that and run on their own collection schedule, every 30 minutes by default and editable like any Oracle EM metric. So the page is on-demand and the alerting is scheduled.
Where this fits
The Index Advisor is one of five advisors that arrived in the August 2026 release, the one that turned the PostgreSQL Plug-in for Oracle Enterprise Manager from something that tells you what is happening into something that tells you what to do about it. It ships as two builds with the same features, 24.1.1.0.0 for Enterprise Manager 24ai and 13.5.15.0.0 for Enterprise Manager 13.5, and everything in it is additive: your targets, thresholds, schedules, and credentials carry forward unchanged.
This is the first of four posts on those advisory features. The next three cover Plan Analysis and the Plan Drift Advisor, on capturing the plan a slow query actually ran and knowing when it leaves a plan you certified; the Vacuum Advisor and the xmin horizon, on why autovacuum falls behind on a table and what pins the transaction horizon; and Workload History, on replaying weeks of statement statistics from a history store that lives on the agent rather than in your Enterprise Manager repository.
If you are upgrading from 13.5.12, start at Monitoring Readiness on each target. It probes the target when the page loads and tells you, feature by feature, what that target is still missing, including which of these two extensions are installed in which databases.
We are demonstrating all five advisors live on September 23, 2026, including the Index Advisor with both extensions installed. Details and registration.
- What's new in this release → docs.integrationplumbers.io/postgresql/whats-new.html
- Index Advisor reference → docs.integrationplumbers.io/postgresql/index-advisor.html
- Installing the optional extensions → docs.integrationplumbers.io/postgresql/prerequisites.html#optional-extensions
- The plug-in → integrationplumbers.io/postgresql-plugin
