> ## Documentation Index
> Fetch the complete documentation index at: https://docs.tensor9.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Query Plans in Explain

> Understand full PostgreSQL plans, owner-only access, estimated costs and unavailable planning evidence.

> [!NOTE]
>
> **Planned reporting, October 25, 2026. Owner: Tensor9 Cloud Adapter team.** PostgreSQL plan capture is under development and is being qualified by operation and backend. This page describes its reporting contract; it does not establish availability in your installed release. Confirm coverage for your exact service, operation, PostgreSQL backend and adapter version before relying on a plan. Ordinary Explain support does not imply SQL-plan support.

An adapter explanation can tell you how an origin request maps to a target operation. For a PostgreSQL-backed operation, a second question matters: how would PostgreSQL access the data for the SQL that implements that request?

The planned integration will include PostgreSQL's own `EXPLAIN (FORMAT JSON)` result in the same private report. You will retrieve it through the dedicated synthetic AWS `t9` service, alongside the adapter's decisions and operation evidence. The application will keep receiving the native service response.

## The mental model

Read three kinds of evidence together:

| Evidence | Question it answers |
| - | - |
| Adapter decisions and facts | Which mapping, validation or branch did the adapter select? |
| PostgreSQL plan | What plan did PostgreSQL estimate for an instrumented SQL statement in the observed database context? |
| Native result and target-state observations | What did the service return, and what effects were independently observed? |

A plan is an estimate produced by the database planner. It does not prove that an operation committed, that a row was returned, or that the native request used that exact plan.

## How collection will work

![Native request handling and bounded PostgreSQL plan collection run independently. The report owner later retrieves a collecting or sealed report through the IAM-authenticated t9 service.](https://mintcdn.com/tensor9/i8WBQ0EvX8CVcW3l/images/diagrams/explain/postgres-collection-wide-dark.svg?fit=max\&auto=format\&n=i8WBQ0EvX8CVcW3l\&q=85\&s=639ec7421b0ec17acba0a71ae2499fd7)

![Native request handling and bounded PostgreSQL plan collection run independently. The report owner later retrieves a collecting or sealed report through the IAM-authenticated t9 service.](https://mintcdn.com/tensor9/i8WBQ0EvX8CVcW3l/images/diagrams/explain/postgres-collection-stacked-dark.svg?fit=max\&auto=format\&n=i8WBQ0EvX8CVcW3l\&q=85\&s=97466ba23309d7d9d556d3fe5d1db52b)

![Native request handling and bounded PostgreSQL plan collection run independently. The report owner later retrieves a collecting or sealed report through the IAM-authenticated t9 service.](https://mintcdn.com/tensor9/i8WBQ0EvX8CVcW3l/images/diagrams/explain/postgres-collection-wide-light.svg?fit=max\&auto=format\&n=i8WBQ0EvX8CVcW3l\&q=85\&s=7df7a0f349cca4381aa7b8953728a76d)

![Native request handling and bounded PostgreSQL plan collection run independently. The report owner later retrieves a collecting or sealed report through the IAM-authenticated t9 service.](https://mintcdn.com/tensor9/i8WBQ0EvX8CVcW3l/images/diagrams/explain/postgres-collection-stacked-light.svg?fit=max\&auto=format\&n=i8WBQ0EvX8CVcW3l\&q=85\&s=dbed68fa4b6abe7ead6caec1448d1d3f)

In `Explain` mode, a completed preview returns `409 ExplainedNotPerformed` in the service protocol. `ExecuteAndExplain` preserves the ordinary operation response. Both carry a report receipt when capture is admitted; neither places a query plan inside the service response.

The observer may run before or after the service response. The diagram shows the independent lifetimes, not a required ordering between native execution and planning.

The adapter will use the operation's actual compiled SQL and encoded parameters, rather than reconstructing a similar query from a log. A separate, authenticated read-only PostgreSQL connection will perform the planning. It must use the same selected database and a matching database identity and session configuration, including relevant row-security, search-path and planner settings. An administrative connection or a different service's database is not a substitute.

The observer will not execute the native statement again. PostgreSQL's `ANALYZE` option runs the statement; plain `EXPLAIN` plans it. Tensor9 plan collection will not use `EXPLAIN ANALYZE`, including for writes. See the [PostgreSQL EXPLAIN reference](https://www.postgresql.org/docs/current/sql-explain.html).

Planning errors, unavailable capacity and timeouts will affect the diagnostic evidence, rather than replace the native result or retry the native operation. A preview will remain effect-free: its planning path must not perform the proposed write.

### A committed-state estimate

The payload's `planningContext` will be `committed-state-observation`. This names a separate observation of committed database state. It does not claim to share the original operation's transaction snapshot, see its uncommitted changes, or reproduce transaction-local temporary objects and settings.

Even when SQL and parameters match, database statistics, committed data, configuration or the prepared-statement cache can differ from the original execution. Treat this plan as PostgreSQL's estimate at its recorded observation time. Do not label its estimated rows or costs as actual execution measurements.

If the adapter cannot reproduce the required context safely, the report will say the plan is unavailable or unsupported. It must not silently plan through a more privileged identity or invent a comparable query.

## Retrieve the plan through the existing report workflow

Plan collection will use the same request receipt and report ID as the surrounding explanation:

1. Preserve the native response and its `x-t9-explain-id` and `x-t9-explain-expires-at` headers.
2. Call `T9CloudAdapter.GetExplanation` on the install's published `t9` endpoint. Use the original report owner's IAM credentials, including the original session token when temporary credentials are used, and SigV4 signing service `t9`.
3. Decode the outer AWS JSON response. Its `body` is a string containing the selected rendering. In JSON format, inspect `facts` for an assertion with `type: "postgres-explain"`.
4. If the report is still collecting, retain its revision and poll the same ID with bounded backoff. Never replay a write to wait for a plan.
5. Export needed evidence before the owner acknowledges the sealed revision or retention ends.

See [Explaining SDK Requests](https://docs.tensor9.com/cloud-adapter/debugging/explain-sdk.md) for the signed request and retrieval examples. The developer CLI's [existing-report mode](https://docs.tensor9.com/cloud-adapter/debugging/explain-sdk.md#retrieve-an-existing-report-with-t9dev) prints the rendered body directly; it does not acknowledge the report.

Planning will not extend report retention. The default remains 24 hours from report creation, and reads do not renew it. Report reads and acknowledgment will not themselves generate recursive SQL plans.

## Read a PostgreSQL plan fact

This illustrative JSON is **one fact excerpt**, not a complete report or a production measurement. The relation and predicate are example data; actual full plans retain the values PostgreSQL returns. Relation names identify physical PostgreSQL tables and need not match the origin service resource name.

```json theme={null}
{
  "id": "fact-postgres-lookup",
  "provenance": {
    "basis": "Observed",
    "source": "postgres.explain",
    "observedAt": "2026-10-25T08:00:00Z"
  },
  "assertion": {
    "factVersion": 1,
    "type": "postgres-explain",
    "payload": {
      "planningContext": "committed-state-observation",
      "statementCode": "dynamodb.get-item.select",
      "statementOrdinal": 1,
      "plan": [
        {
          "Plan": {
            "Node Type": "Index Scan",
            "Scan Direction": "Forward",
            "Index Name": "ddb_example_orders_pkey",
            "Relation Name": "ddb_example_orders",
            "Alias": "ddb_example_orders",
            "Startup Cost": 0.29,
            "Total Cost": 8.31,
            "Plan Rows": 1,
            "Plan Width": 64,
            "Index Cond": "((pk = 'example-123'::text) AND (sk = ''::text))"
          }
        }
      ]
    }
  }
}
```

| Field | How to use it |
| - | - |
| Fact ID, scope and provenance | Connect the observation to the operation's step and recorded observation time. |
| `statementCode` | Identify the instrumented SQL statement. It is not a SQL text dump. |
| `statementOrdinal` | Distinguish the statement's one-based position within the operation. A plan for one position does not prove coverage of every other statement. |
| `planningContext` | Retain the committed-state limitation when comparing this estimate with native execution. |
| `plan` | Preserve the complete native PostgreSQL JSON array and nested `Plans` tree. Do not reduce it to a node-name summary. |

Human and Agent renderings will carry the same complete plan data as JSON. Choosing a format will not change execution, report ownership or evidence scope.

### Interpret the example

The example plans an `Index Scan` through `ddb_example_orders_pkey` with the shown index condition. `Plan Rows: 1` estimates rows emitted by that node. The costs are planner units, not milliseconds or currency, and a parent's cost includes its children. A `Seq Scan` is not automatically a problem; table size and the fraction of rows needed can make it appropriate. PostgreSQL's [plan-reading guide](https://www.postgresql.org/docs/current/using-explain.html) explains these distinctions.

For an investigation:

1. Check that the fact belongs to the request, backend and observation time you are investigating.
2. Inspect predicates and child nodes, not only the root node name. A small estimate at the root can sit above substantial scan or join work.
3. Compare plans only when the statement, parameters and relevant database context are comparable. Preserve both complete reports when comparing revisions or configurations.
4. Use native status and independent state observations to establish effects. Use separately approved performance measurements to establish latency or throughput.

Do not change an index or database setting solely because one estimate looks expensive. First identify the relevant statement, current schema, statistics and workload; a plan is one piece of evidence.

## Full plans are private customer data

Full PostgreSQL JSON can include relation and index names, predicates and application literals embedded in expressions. The planned output intentionally preserves them for the exact authenticated report owner. It is not an anonymized or automatically sanitized summary.

A new assumed-role session does not inherit another session's report. A Tensor9 account API key, target-cloud credential or general permission to read other reports does not grant access to a plan-bearing report owned by someone else.

Separate SQL text, parameter arrays, credentials and raw PostgreSQL error messages will not be additional plan fields. Values can still occur **inside the PostgreSQL plan itself**. Review and sanitize an exported copy before sending it to support or another person; preserve the original privately when it is needed as evidence.

## When a plan is missing

An early revision can precede planning completion. After bounded collection, a report may retain an explicit unknown or unavailable observation instead of a plan. Absence alone does not mean no SQL ran, the operation failed, the statement was cheap or an index was used.

| Reason in the planned reporting path | Meaning and next step |
| - | - |
| `postgres-plan.unsupported-context` | Required database or session context cannot be established safely. Confirm exact operation/backend support and the installed configuration. |
| `postgres-plan.capacity` | An independent planning connection cannot be admitted within the configured capacity. Check the generated database budget; native pools must retain their own allocation. |
| `postgres-plan.admission` | A statement, argument or outstanding-work budget refused observation. Inspect any omitted-candidate count and report limitations. |
| `postgres-plan.deadline` | The bounded planning lifetime ended. Keep the native result and the evidence that was collected. |
| `postgres-plan.oversize` | The complete plan exceeds its atomic allowance. Do not treat a partial JSON tree as the result. |
| `postgres-plan.unavailable` or `postgres-plan.dropped` | Planning could not supply evidence, or its registered work did not complete. Do not infer a native outcome from this diagnostic failure. |
| `postgres-plan.expired` | The report's fixed capture lifetime ended. Use previously exported evidence. |

The current plan-fact implementation has a 256 KiB encoded payload allowance, with additional whole-report and work budgets. A plan must fit in full or be unavailable; collection will not silently truncate nodes or expressions. Batch operations may therefore include plans for some statements and an explicit omission count for others.

The planned generated connection budgets reserve one observer connection per active backend profile in addition to native request capacity. Shared planner owners and separate backend owners must be counted according to the deployment's actual composition. This reservation does not authorize raising a PostgreSQL role limit or changing a live database automatically.

An operation that uses a non-SQL target has no PostgreSQL target plan. A stored-function plan may expose only the function call, rather than SQL executed inside that function. General Explain support, a successful origin request and persistence of the explanation in PostgreSQL do not prove workload SQL-plan coverage.

## Common questions

**Does a PostgreSQL-backed adapter always include a plan?**

No. Support is specific to the operation, statement and backend, and this integration is still under development. An operation may reach unsupported SQL or a context that cannot be observed safely. Check the installed release's coverage and the report's limitations.

**Does an INSERT plan mean the row was inserted?**

No. Planning-only EXPLAIN does not execute the INSERT. An `ExecuteAndExplain` operation can separately commit its ordinary write; the plan fact alone does not establish that commit.

**Will reading the report rerun the query?**

No. `GetExplanation` reads stored evidence. It does not replay the origin operation, execute its SQL or ask PostgreSQL to collect another plan.

**Can I recover missing planning evidence by sending the same write again?**

Do not replay an uncertain write for diagnostics. Preserve its native result, inspect current resource state using an appropriate read, and use the existing report. A new request is a new operation and requires its own effect and idempotency decision.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.