{
  "type": "disk_spill_hash",
  "severity": "warning",
  "message": "Hash operation spilled to disk: 8 batches, peak 25067kB ...",
  "context": {
    "batches": 8,
    "peak_kb": 25067,
    "estimated_mb": 195.8,
    "disk_kb": 0,
    "parallel": false,
    "loops": 1,
    "node_type": "Hash"
  }
}

A consumer reads context.estimated_mb = 195.8 and computes the
required work_mem directly — no regex on the message.

Each check declares its own context shape; the fields are the
same ones documented in the check's gather_info docstring.

### Structured plan output

`explain` returns a compact `plan_nodes` tree alongside `issues`.
Only the fields that matter for reasoning are kept:

- `node_type`, `relation_name`, `index_name`
- `actual_rows`, `plan_rows`, `actual_loops`
- `rows_removed_by_filter`, `heap_fetches`, `shared_read_blocks`
- `sort_method`, `sort_space_type`, `sort_space_used`
- `hash_batches`, `peak_memory_usage`
- `parallel_aware`

The report also carries `root_meta` — top-level EXPLAIN metadata
(`Planning`, `Planning Time`, `JIT`, `Triggers`, `Execution Time`) —
so an LLM can read `JIT.Timing.Total` without a second EXPLAIN callThe report also carries `root_meta` — top-level EXPLAIN metadata
(`Planning`, `Planning Time`, `JIT`, `Triggers`, `Execution Time`) —
so an LLM can read `JIT.Timing.Total` without a second EXPLAIN call..

The list of fields is configurable — see [Configuration](#configuration).

## Installation

Requires Python 3.10+.

```bash
git clone https://github.com/Nick-Msk/pg-explain-mcp.git
cd pg-explain-mcp

python3.12 -m venv .venv
source .venv/bin/activate      # Windows: .venv\Scripts\activate

pip install --upgrade pip
pip install -e .

python -c "from pg_explain_mcp import server; print('OK')"
# → OK

pytest -v

# SQL as argument
pg-explain-parse "select * from t limit 10"

# SQL from a file
pg-explain-parse query.sql

# SQL from stdin
cat query.sql | pg-explain-parse

# Parse a saved plan JSON instead
pg-explain-parse --plan plan.json
cat plan.json | pg-explain-parse --plan -

# Flags
pg-explain-parse --json              # flat node list as JSON
pg-explain-parse --no-analyze        # plan only, don't execute
pg-explain-parse --no-buffers        # skip BUFFERS
pg-explain-parse --all-fields        # keep zero-valued numeric fields
pg-explain-parse --marker-tabs 3     # tab padding in the text tree
pg-explain-parse --with-meta         # wrap JSON as {meta, plan}

python -m pg_explain_mcp.config --init   # rebuild from schema + seed
python -m pg_explain_mcp.config --show   # print current state

--init drops and recreates the seed tables only. The audit log
(config_audit) and the writer tag (_audit_session) survive, so
the history of config changes is preserved across rebuilds. Note
that --init resets every param to its seed default — see
Audit log.

## Configuration

### Connection

The server reads PostgreSQL connection parameters from environment
variables:

| Variable         | Default     | Description                |
|------------------|-------------|----------------------------|
| `PG_HOST`        | `localhost` | PostgreSQL host            |
| `PG_PORT`        | `5432`      | PostgreSQL port            |
| `PG_USER`        | `postgres`  | Database user              |
| `PG_PASSWORD`    | —           | Database password          |
| `PG_DATABASE`    | `postgres`  | Database name              |
| `TARGET_DB_TYPE` | `postgres`  | Database (pg/orcl/mysql..) |

### Check registry

Enabled checks and their thresholds live in a small SQLite database at
`config/checks.db`. The database is created automatically on first
`explain` if missing, and rebuilt from scratch by an explicit `--init`:

```bash
python -m pg_explain_mcp.config --init   # rebuild from schema + seed
python -m pg_explain_mcp.config --show   # print current state

# Disable NestedLoopCheck without touching the code.
sqlite3 config/checks.db \
  "update checks set enabled = 0 where name = 'NestedLoopCheck';"

# Raise SeqScanCheck's threshold from 1000 to 5000 rows.
sqlite3 config/checks.db \
  "update check_params set value = '5000'
   where num = 1 and param = 'threshold_rows';"

sqlite3 config/checks.db \
  "insert into plan_fields (database, raw, key, enabled)
   values ('postgres', 'Total Cost', 'total_cost', 1);"

sqlite3 config/checks.db \
  "insert into plan_fields (database, raw, key, enabled)
   values ('postgres', 'Total Cost', 'total_cost', 1);"

## Usage with Continue.dev

Ready-to-use configuration files are available in
[`config_mcp/`](config_mcp/):

- [`config_mcp/mcpServers/pg-explain.yaml`](config_mcp/mcpServers/pg-explain.yaml)
  — MCP server registration.
- [`config_mcp/postgres-agent.md`](config_mcp/postgres-agent.md)
  — system prompt for an assistant that knows how to use `pg-explain`
  and a generic PostgreSQL MCP server, with per-check guidance.

## Companion to `universal-db-mcp`

`pg-explain-mcp` is designed to be used **alongside** a
general-purpose PostgreSQL MCP server, not to replace it. In the
reference setup we use
[`universal-db-mcp`](https://github.com/Anarkh-Lee/universal-db-mcp)
configured for the same database, and the two servers serve different
purposes:

| Server             | Purpose                                                 |
|--------------------|---------------------------------------------------------|
| `universal-db-mcp` | General SQL execution: `SELECT`, schema exploration, ad-hoc queries. |
| `pg-explain-mcp`   | Plan analysis: `EXPLAIN ANALYZE`, structured bottleneck detection, index/parameter inspection. |

The assistant decides which to call based on the question:

- *"How many rows are in `orders`?"* → `universal-db-mcp`.
- *"Why is this query slow?"* → `pg-explain-mcp`.

All examples in [`usage_examples/`](usage_examples/) were captured with
both servers loaded. The agent configuration in
[`config_mcp/postgres-agent.md`](config_mcp/postgres-agent.md)
describes both and includes guidance on when to prefer one over the
other.

### Audit log

Every change to the config database is recorded in `config_audit`.
The table is populated automatically by triggers — no application
code writes to it directly.

**What is tracked:**

| Table          | Column     | Op recorded on        |
|----------------|------------|-----------------------|
| `checks`       | `enabled`  | insert / update / delete |
| `check_params` | `value`    | insert / update / delete |
| `plan_fields`  | `enabled`  | insert / update / delete |
| `plan_fields`  | `key`      | insert / update / delete |

**Row shape:** `(id, table_name, column_name, optype, ts, old_value,
new_value, who)`. `optype` is `'I'` (insert), `'U'` (update), or
`'D'` (delete) — so a delete and an update-that-sets-NULL are
distinguishable. `ts` is UTC ISO-8601 with milliseconds.

**Writer tags** (`who`):

| Tag        | Set by                                                         |
|------------|----------------------------------------------------------------|
| `system`   | Default. Manual `sqlite3` edits, `--init` overhead.            |
| `llm`      | MCP calls to `set_checker_value` / `reset_checker_value`.      |
| `seed`     | `seed.sql` inserts during `--init`. Hidden by default in `history_checker_values`. |

**Reading the log via MCP:**

history_checker_values() # all non-seed rows
history_checker_values(table_name="check_params") # one table
history_checker_values(optype="U") # updates only
history_checker_values(count=20) # newest 20
history_checker_values(include_seed=True) # include --init noise

**Reading the log via SQL:**

```bash
sqlite3 config/checks.db \
  "select ts, optype, table_name, column_name, old_value, new_value, who
   from config_audit order by id desc limit 20;"
Survives --init. --init drops and rebuilds the seed tables
(checks, check_params, plan_fields, tags, databases) but
leaves config_audit and _audit_session intact. This makes it
possible to answer "who changed this, and when?" across rebuilds.

Caveat: --init resets every param to its seed default. If you
had a non-default value in check_params and you re-run --init,
the value is lost — but the change that produced it is preserved in
config_audit. Before running --init in a shared environment,
check history_checker_values() for recent edits.

### Why not use `pg-explain-mcp` alone?

You can — the server is self-contained and exposes `list_tables` for
schema inspection. Disabling `universal-db-mcp` is perfectly fine if
you only care about plan analysis.

In practice, however, mixing the two is more convenient: `pg-explain`'s
`EXPLAIN ANALYZE` **actually executes** every query, which is expensive
for large tables. For anything that does not need a plan — a row count,
a lookup, a quick sanity check — `universal-db-mcp` runs the same query
in read-only mode with much less overhead.

The split also reduces the blast radius of an LLM mistake: a bad
`SELECT` on `universal-db-mcp` is cheap, while a bad
`EXPLAIN ANALYZE` on `pg-explain-mcp` can be expensive. Keeping the
cheap path as the default makes the setup more robust.

### Setup

1. Copy `config_mcp/mcpServers/pg-explain.yaml` into your
   workspace's `.continue/mcpServers/` directory.
2. Replace the placeholders:
   - `command:` — full path to the Python interpreter inside your
     `.venv`.
   - `PG_USER`, `PG_PASSWORD`, `PG_DATABASE` — your PostgreSQL
     credentials.
3. (Optional) Copy `config_mcp/postgres-agent.md` into
   `.continue/agents/` to use it as a custom agent prompt.
4. In VS Code: `Cmd+Shift+P` → **`Continue: Reload Config`**.
5. Open a new chat in **Agent Mode** (not Chat, not Edit).

### Example

Prompt:

> Use the pg-explain tool to analyze:
> `SELECT val FROM onek1 WHERE hundred BETWEEN 5 AND 55;`

Response:

## Test fixtures

The repository ships a PostgreSQL extension
[`mcp_explain_tool`](fixtures/) that creates empty tables and
`fill_*` / `clear_*` procedures for every check. It is versioned
independently from the Python package.

```bash
cd fixtures
make install

create extension mcp_explain_tool;
call mcp_explain_tool.fill_all(1000000);

pg-explain-mcp/
├── src/
│   └── pg_explain_mcp/
│       ├── __init__.py
│       ├── server.py         # MCP entry point — exposes tools
│       ├── parse.py          # pg-explain-parse CLI
│       ├── db.py             # connection + EXPLAIN + schema queries
│       ├── analyzer.py       # PlanNode, checks, new_* pipeline
│       └── config.py         # SQLite-backed check registry + CLI
├── config/                   # SQLite config: schema, seed, checks.db
├── tests/                    # unit tests for checks and helpers
├── fixtures/                 # mcp_explain_tool PostgreSQL extension
├── usage_examples/           # real-world runs, one set per check
├── config_mcp/           # Continue.dev MCP + agent config
├── images/                   # screenshots
├── pyproject.toml
├── CHANGELOG.md
├── DISCLAIMER.md
├── README.md
└── LICENSE