explainQuery

Return the raw EXPLAIN (FORMAT JSON) plan for a statement, either recovered from pg_stat_statements by queryid (full untruncated text) or supplied directly as sql.

read-onlyany connectioncan return data values

Synopsis

explainQuery([analyze], [params], [plan_as_role], [queryid], [settings], [sql], [timeout_ms])

Description

Return the raw EXPLAIN (FORMAT JSON) plan for a statement, either recovered from pg_stat_statements by queryid (full untruncated text) or supplied directly as sql. Runs in a read-only transaction bounded by statement_timeout. Statements with $n placeholders are planned with GENERIC_PLAN unless concrete params are supplied, in which case the statement is PREPAREd and planned with real values. analyze:true runs EXPLAIN (ANALYZE, BUFFERS), which really executes the statement, and is honoured only after the plan is proven free of any ModifyTable node -- so data-modifying statements, including data-modifying CTEs, are never executed; it also requires an explicit timeout_ms.

Returns the plan verbatim plus generic/analyzed/read_only flags and the pg_stat_statements row; no heuristics and no generated DDL, the plan is yours to interpret. Every plan carries a Settings block (EXPLAIN SETTINGS) naming the settings that differ from the built-in default, because the plan is built in THIS server's session and not in the one the statement really runs in -- work_mem alone can change the algorithm rather than the cost, turning a HashAggregate into a Sort plus GroupAggregate.

Compare it against hostCapacity.overrides, which reports the per-role and per-database settings pg_settings cannot show: where they differ, this plan is not the plan production gets -- and plan_as_role then plans it under what that role actually carries, so the difference between the two plans becomes the finding rather than a caveat. planning_environment reports what was applied and, for plan_as_role, what was skipped.

Parameters

analyze optionalboolean

run EXPLAIN (ANALYZE, BUFFERS), which really executes the statement. Requires timeout_ms. Ignored with an explanatory 'note' if the statement modifies data or could only be planned generically. Default false

params optionalarray

concrete values for the statement's $1..$n placeholders, in order. Supply these for a real (non-generic) plan; required to use analyze. Values are bound as literals of unknown type and coerced by PostgreSQL to the inferred parameter types; use null for SQL NULL

plan_as_role optionalstring

plan under what this role carries in pg_db_role_setting, filtered to planner settings. A per-database entry (ALTER ROLE ... IN DATABASE) overrides the role-wide one, as the server itself applies them. Non-planner entries such as search_path or statement_timeout are reported under skipped_from_role rather than dropped silently

queryid optionalstring

pg_stat_statements queryid as a decimal string (it is a 64-bit value and does not survive JSON number precision). Mutually exclusive with 'sql'

settings optionalobject

planner settings to apply for this plan only, e.g. {"work_mem": "512MB"}. Applied with set_config(is_local) so they revert with the transaction and cannot leak to another session. Allowlisted to settings that change a PLAN; an unknown name is refused and nothing is planned, rather than ignored. With analyze the statement is EXECUTED under them only within a budget taken from the host capacity declared for this connection (host_ram_mb and host_vcpus): the worst case the plan's memory limits permit must stay within a share of RAM (10% unless budgets.ini says otherwise), and the plan may use at most one parallel worker per four vCPUs (likewise). With no declared capacity no settings change is executed at all. Either way a refused plan is still returned, analyzed stays false, and planning_environment.execution_budget carries the arithmetic. plan_as_role alone is not budgeted: it is what that role already runs with

sql optionalstring

a single SELECT/INSERT/UPDATE/DELETE/MERGE/WITH/TABLE/VALUES statement to explain. Utility statements (SET, CREATE, VACUUM, ...) are rejected. Mutually exclusive with 'queryid'

timeout_ms optionalinteger

statement_timeout for the explain, in milliseconds, clamped to [100, 30000]. Required when analyze is true; defaults to 5000 for plan-only calls

Also accepts connection, described once under arguments every tool takes.

Output

An EXPLAIN plan and what produced it. An absent extension or a missing grant is reported as {error, hint} instead.

Scope

Never runs across more than one connection: the same statement is rarely valid in another database, and with analyze it would execute once per member.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "explainQuery",
    "arguments": {
      "sql": "SELECT id, status FROM shop.orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20",
      "params": [
        88213
      ]
    }
  }
}

Result

{
  "plan": [
    {
      "Plan": {
        "Node Type": "Limit",
        "Startup Cost": 0.43,
        "Total Cost": 72.18,
        "Plan Rows": 20,
        "Plans": [
          {
            "Node Type": "Index Scan",
            "Index Name": "orders_customer_created_idx",
            "Relation Name": "orders",
            "Index Cond": "(customer_id = '88213'::bigint)",
            "Plan Rows": 41
          }
        ]
      },
      "Settings": {
        "work_mem": "32MB"
      }
    }
  ],
  "generic": false,
  "analyzed": false,
  "read_only": true,
  "timeout_ms": 5000,
  "source": "sql",
  "sql": "SELECT id, status FROM shop.orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20"
}

Invented values on a fictional shop database, shaped by and checked against this tool's output schema. Real output is returned as structuredContent to clients that negotiate MCP 2025-06-18 or later.

See also

evaluateIndex, duplicateIndexes, tableBloat, indexBloat, checkKey