Integration Plumbers // Docs

Documentation for Integration Plumbers products.

Plan Analysis

When a query gets slower, the useful evidence is the plan it actually ran, and that plan is usually gone by the time anyone looks. Plan Analysis keeps it: the PostgreSQL plug-in harvests execution plans that auto_explain already wrote to the server log, stores them on the agent, and runs five detection rules over each one, so a plan that has something wrong with it arrives with the finding and the change to make already attached.

Prerequisites for this page

  • auto_explain installed on the target and configured for capture — see Plan capture (auto_explain). The plug-in configures the module but never installs it.
  • auto_explain.log_min_duration set to 0 or higher, auto_explain.log_format = json, and auto_explain.log_analyze = on. Apply all three from Configure auto_explain on Monitoring Readiness.
  • The pg_read_server_files grant on the monitoring role, so the plug-in can read the server log — see The server log read grant.
  • Every panel on this page is job-backed, so Preferred Credentials must be set for the target’s host. If a job aborts, the page raises “Unable to run job. Verify Preferred Credentials are set for this target.” See Unable to run job.
  • A timestamp-first (%m-led) log_line_prefix and the stderr log destination. The harvester does not parse csvlog or jsonlog.
  • Recommended, not required: auto_explain.log_verbose = on and compute_query_id = on, so captured plans carry real query ids — see Statement statistics (pg_stat_statements).

Where to find it: on a PostgreSQL Database target, left navigation tree ▸ database name ▸ Plan Analysis. The target’s PostgreSQL drop-down menu carries the same entry under the database name.

In this page: How plans are captured · The Plan Analysis page · The five insights · Top recommendation banner and Index Advisor link · Plan Insights alerts · Behaviors and caveats

How plans are captured

Capture is passive. auto_explain writes each qualifying statement’s plan to the PostgreSQL server log during that query’s own execution, and the plug-in reads those plan bodies back out of the log. The plug-in never re-executes a statement and never issues an EXPLAIN of its own as part of capture or configuration. The only EXPLAIN that executes a statement is the Fix Workbench: Test a Rewrite panel on Plan Drift Advisor, which runs EXPLAIN (ANALYZE, FORMAT JSON) once, and only when you click Run Explain. The Index Advisor’s HypoPG simulation issues the one other EXPLAIN in the product, a plan-only EXPLAIN (FORMAT JSON) over a synthetic lookup that executes nothing.

The harvester finds the current log file with pg_current_logfile() and reads it with pg_read_file() over the same JDBC connection the plug-in already uses. There is no OS-level file access, so capture behaves identically whether the agent runs on the database host or somewhere else. If the log cannot be read, the plug-in logs a warning and skips the harvest; the collection itself does not fail.

Each capture is stored in the agent-local history store with:

Stored with every capture Notes
Plan JSON tree The full node tree as auto_explain recorded it, including actual rows and timings
Query text Used for the list, the banner, and the synthetic query id
Query id The real queryid, or a synthetic syn: id (below)
Capture time When the plan was harvested
Trigger threshold The auto_explain.log_min_duration value that let this statement through
Mean and total execution time Drives the list’s Mean (ms) and Calls / Total (ms) columns

Plan bodies stay on the agent. Nothing plan-body-sized is uploaded to the management repository.

Query identifiers and the syn: fallback

Captured plans are keyed by query id. When a plan arrives without one (usually because auto_explain.log_verbose or compute_query_id is off, or pg_stat_statements is not set up), the plug-in computes a synthetic id of the form syn:<hash> from the literal-normalized query text. Grouping, plan history, drift detection, and baselines all keep working on synthetic ids, and the syn: prefix makes them visible at a glance.

There is nothing to do about this. If you later enable the query-id settings, the affected statements move from syn: ids to real ids and start a fresh lineage, so expect their history to restart from that point.

How long captures are kept

Captured plans default to a 90-day retention window, set on the Retention Policies page. The archive is additionally bounded by a plan-archive size ceiling of 100 MB with oldest-first eviction, which is not on that page: it is set through the PostgreSQL - Set Plan Archive Size Ceiling job, parameter Captured Plans Size Ceiling (MB) — see Store size and disk reclaim. Only flagged poor-performing queries are archived, and any capture that represents an accepted baseline is exempt from eviction.

The Plan Analysis page

The page header states the capture model directly: “Plans are captured by auto_explain during the query’s own execution — no re-execution. Only statements exceeding the threshold below are captured.”

  1. Open Plan Analysis on the PostgreSQL Database target.
  2. Read the Overview KPI tiles (below).
  3. Set the high-cost line: type a value into High-cost threshold (optimizer Total Cost) and click Save threshold. The confirmation reads “High-cost threshold saved.” and the KPI tiles reload straight away.
  4. Optionally restrict harvesting to an off-peak window in Capture Window.
  5. Work the Historical Query Insights list: choose a Sort, choose how many Rows to show, and click Refresh.
  6. Expand a row to see the captured plan tree and one recommendation card per detected insight.

The Plan Analysis page showing the Overview KPI tiles, the Capture Window editor, and the Historical Query Insights list The whole page: KPI band, capture-window editor, and the captured-query list.

Overview KPIs

Tile What it shows
Captured plans How many plans are in the archive for this target
High-cost plans How many of them have an optimizer Total Cost above the high-cost threshold
Capture threshold The auto_explain.log_min_duration value in effect on the database. Shows off when the setting is -1, and “Configured (value unavailable)” when the value cannot be read

The Capture threshold tile is read from the database rather than from anything you set here, so the page always tells you what is actually being captured. Its tooltip: “auto_explain.log_min_duration in effect at the most recent capture. This setting decides which statements are captured.”

Two thresholds, two different units

Getting these two confused is the most common way to misread the page.

Setting Unit Where you set it What it decides
auto_explain.log_min_duration Milliseconds of execution time Monitoring Readiness, or postgresql.conf Which statements get captured at all. A statement running longer than this many milliseconds has its plan written to the log; -1 disables capture
High-cost threshold Optimizer cost units This page, under Overview Which captured plans are labelled high-cost. A plan is high-cost when its optimizer Total Cost exceeds this value

The high-cost threshold changes nothing about what is captured. It only moves the line the High-cost plans KPI counts against. The on-page hint says it plainly: “A plan is ‘high-cost’ when its optimizer Total Cost exceeds this value (cost units, not ms — separate from the capture threshold). Tune to your workload.” Cost units are instance-relative, which is why the default of 100000 is a starting point rather than an answer.

Capture Window

Log harvesting can be confined to an off-peak window. Tick Restrict plan harvest to an off-peak window, set From and To, and click Save.

The window is a preference. Turning capture on or off is done on Monitoring Readiness, not here.

Historical Query Insights

Column What it shows
Query The captured statement text, truncated
Database The database the capture came from
Insights A count of the pathologies detected on this capture, colored by the highest severity present. A clean plan shows a dash
Mean (ms) Mean execution time for the statement
Calls / Total (ms) Call count and total execution time
Captured When the plan was harvested

Sort by Most recent (the default), Total exec time, Mean exec time, or Calls. Rows offers 25, 50 (default), 100, and 200.

A dash is not a gap in the data. It means the detection rules found nothing wrong with that plan. The named per-pathology badges appear when you expand the row; a clean capture reads “No insights detected for this capture.”

Expanding a row

Click a row to expand it. The plug-in loads that capture’s plan body and renders the plan tree, showing each node’s type, cost, estimated versus actual rows, and actual time, nested the way it executed. Beneath the tree, under a Recommendations heading, sits one card per detected insight that carries concrete advice. Each card names the pathology, states the evidence behind it, and gives the change to make. Where the advice names a parameter value or a SQL statement, the card carries a Copy button for the copy-ready text.

Expanding a row loads the stored plan body and renders it as a tree.

An expanded Plan Analysis row with the plan tree and a recommendation card Per node: node type, cost, estimated versus actual rows, and actual time. Recommendation cards sit below.

Recommendations are review-and-run: you copy them and run them in your own tooling, at a time of your choosing. The plug-in never applies one.

The five insights

Five detections are surfaced against each newly captured plan. Each carries a severity, which in practice is Medium or High: none of the five rules fires below Medium.

Insight What it detects Typical recommendation
Insufficient Index A sequential scan that reads a large number of rows and throws most of them away through its filter, returning only a few A B-tree index covering the filter columns, so the scan can seek instead of reading and discarding
Misestimate A plan node whose estimated row count is far away from the rows it actually produced, in either direction Run ANALYZE on the affected table. If the gap persists, raise the statistics target on the filter or join columns, or default_statistics_target
Stale Statistics A scanned table that was analyzed a long time ago, or never, so the planner is working from stale row estimates Run ANALYZE on the table. If it keeps going stale, lower autovacuum_analyze_scale_factor for that table
Slow Sequential Scan A sequential scan discarding a large absolute number of rows by filter across its loops Add a selective index on the table, or refine the query so PostgreSQL stops scanning and filtering the whole table
Lossy Bitmap A Bitmap Heap Scan whose tuple bitmap outgrew work_mem and went lossy, rechecking whole heap pages instead of individual tuples Raise work_mem so the bitmap fits in memory. A more selective index also shrinks the bitmap

A single plan node can trip more than one rule. A sequential scan that is both unselective and large will raise Insufficient Index and Slow Sequential Scan together, because they are different arguments for the same fix.

When an Insufficient Index finding is the highest-impact insight across every capture on the target, a banner appears above the list. It carries the query text, a plain-language summary of the finding, an estimated improvement factor where one could be computed, and an Open Index Advisor → button. The footnote reads “Highest-impact missing-index finding across captured plans.”

The top recommendation banner above the Historical Query Insights list, with the Open Index Advisor button The banner promotes the single highest-impact missing-index finding, ranked by severity and then estimated improvement.

The button navigates to Index Advisor, where the same problem is stated as ranked, ready-to-review index recommendations. The banner is hidden when no Insufficient Index finding exists.

Plan Insights alerts

The plan_insights metric turns these findings into alerts. It publishes one row per detected pathology on each query’s newest captured plan, keyed on query id, database, and insight code, and it raises a standard Enterprise Manager alert when a High-severity insight appears.

Metric Internal name Collected Default Warning Default Critical Occurrences Clears when
Plan Insights plan_insights Every 15 minutes Severity matches HIGH Not defined 1 The insight resolves and drops out of the feed at the next collection

The collection ships enabled, so once plans are being captured no per-feature setup is needed. Alert text:

Thresholds are retunable per insight code through the standard Enterprise Manager threshold UI, and the alert routes through whatever notification connector you already have bound. See Default thresholds for the new metrics for the shipped values across all metrics.

Responding to a plan_insights alert. The alert carries the query id and the insight code. Open Plan Analysis on the named target, find that query in Historical Query Insights, expand it, read the recommendation card for that insight code, and apply the fix in your own tooling.

The related plan_drifts metric alerts on a query running on a plan shape outside its accepted baseline set. It is covered on Plan Drift Advisor.

Behaviors and caveats

↑ PostgreSQL Plug-in documentation