Skip to main content
Glama
YawLabs

@yawlabs/postgres-mcp

by YawLabs
OverviewSchemaRelated ServersScoreDiscussions
TypeScript
Remote

Explain query plan

pg_explain
Destructive

Inspect PostgreSQL query plans to diagnose and optimize SQL performance. Use ANALYZE for real execution details, buffer stats, planner settings, and hypothetical indexes to test improvements.

Instructions

Get the query plan for a SQL statement. By default, this uses plain EXPLAIN (no execution). Set analyze: true to run the query with EXPLAIN ANALYZE - for non-SELECT statements, ALLOW_WRITES=1 is required (since ANALYZE actually executes the statement). Writes executed during EXPLAIN ANALYZE are always rolled back, so you can inspect a plan for an INSERT/UPDATE/DELETE without persisting the mutation. Format is text (default) or json. Pass the raw SQL (not an EXPLAIN-prefixed statement). Planner options (all optional): buffers reports shared/local/temp block hits and is the fastest way to tell a bad plan from a cold cache - it defaults to TRUE whenever analyze is true (matching PostgreSQL 18, which turns it on for you), pass buffers: false to suppress it; requesting it WITHOUT analyze needs PostgreSQL 13+. verbose adds output columns and schema-qualified names. settings (PostgreSQL 12+) lists planner GUCs set away from their defaults - the usual explanation for a plan that looks impossible. wal (PostgreSQL 13+) reports WAL generated and serialize (none|text|binary, PostgreSQL 17+) charges the cost of building the result rows; both require analyze. memory (PostgreSQL 17+) reports memory used by the PLANNER, so it works with or without analyze - use it alone to ask why planning a statement is expensive. generic_plan (PostgreSQL 16+) plans a parameterized statement WITHOUT values for its $1/$2 placeholders and cannot be combined with analyze or params. costs and timing default to true (as in postgres); set either to false to drop those columns, and note timing only applies with analyze. Options that need a newer server than the one connected are rejected with an explicit error naming the required version instead of a confusing parse failure. Set hypothetical_indexes to a list of {table, columns, using?} to ask the planner 'what would the plan be if these indexes existed?' -- requires the HypoPG extension (CREATE EXTENSION hypopg). The hypothetical indexes are torn down at the end of the call, never touching real disk.

Input Schema

TableJSON Schema
NameRequiredDescriptionDefault
sqlYesThe SQL statement to explain. Do NOT prefix with EXPLAIN.
walNoReport WAL generated by the statement. Requires `analyze` (PostgreSQL 13+).
costsNoInclude estimated cost/rows/width. Set false for a terser plan.
formatNoOutput format.text
memoryNoReport memory used by the planner (PostgreSQL 17+). Works with or without `analyze`, since planning happens either way.
paramsNoPositional parameters referenced as $1, $2, ... in the SQL.
timingNoInclude per-node actual timing. Setting it to false REQUIRES `analyze: true` (it is rejected otherwise, not silently ignored); false lowers measurement overhead.
analyzeNoRun EXPLAIN ANALYZE (actually executes the query).
buffersNoReport buffer hits/reads/dirtied. Defaults to TRUE when `analyze` is true (PostgreSQL 18 does the same); pass false to suppress. Requesting it without `analyze` requires PostgreSQL 13+.
verboseNoInclude output columns, schema-qualified names, and triggers.
settingsNoReport planner GUCs set away from their defaults - explains a weird plan (PostgreSQL 12+).
serializeNoCharge the cost of serializing result rows (network-bound queries hide it otherwise). Requires `analyze` (PostgreSQL 17+).
generic_planNoPlan the statement with UNKNOWN values for its $1/$2 placeholders - the plan a prepared statement would get. Cannot be combined with `analyze` or `params` (PostgreSQL 16+).
hypothetical_indexesNoList of indexes the planner should pretend exist for this EXPLAIN. Requires the HypoPG extension. Indexes are session-scoped and reset at the end of the call.

Output Schema

TableJSON Schema
NameRequiredDescriptionDefault
planYesNewline-joined plan text for `format: "text"` (with a trailing truncation marker when POSTGRES_MAX_ROWS chopped it), or the parsed plan array for `format: "json"`.

Schema Changelog

Changes observed during successful MCP inspections. Dates show when Glama detected each change.

  1. Changed14 schema fields changedv0.12.0
    • addedInput schema / $defs
      Added value: +{
+  "__schema0": {
+    "anyOf": [
+      {
+        "type": "string"
+      },
+      {
+        "type": "number"
+      },
+      {
+        "type": "boolean"
+      },
+      {
+        "type": "null"
+      },
+      {
+        "items": {
+          "$ref": "#/$defs/__schema0"
+        },
+        "type": "array"
+      },
+      {
+        "additionalProperties": {
+          "$ref": "#/$defs/__schema0"
+        },
+        "propertyNames": {
+          "type": "string"
+        },
+        "type": "object"
+      }
+    ]
+  }
+}
    • changedInput schema / $schema
      Previous value: -"http://json-schema.org/draft-07/schema#"New value: +"https://json-schema.org/draft/2020-12/schema"
    • removedInput schema / definitions
      Removed value: -{
-  "__schema0": {
-    "anyOf": [
-      {
-        "type": "string"
-      },
-      {
-        "type": "number"
-      },
-      {
-        "type": "boolean"
-      },
-      {
-        "type": "null"
-      },
-      {
-        "items": {
-          "$ref": "#/definitions/__schema0"
-        },
-        "type": "array"
-      },
-      {
-        "additionalProperties": {
-          "$ref": "#/definitions/__schema0"
-        },
-        "propertyNames": {
-          "type": "string"
-        },
-        "type": "object"
-      }
-    ]
-  }
-}
    • addedInput schema / properties / buffers
      Added value: +{
+  "description": "Report buffer hits/reads/dirtied. Defaults to TRUE when `analyze` is true (PostgreSQL 18 does the same); pass false to suppress. Requesting it without `analyze` requires PostgreSQL 13+.",
+  "type": "boolean"
+}
    • addedInput schema / properties / costs
      Added value: +{
+  "default": true,
+  "description": "Include estimated cost/rows/width. Set false for a terser plan.",
+  "type": "boolean"
+}
    • addedInput schema / properties / generic_plan
      Added value: +{
+  "default": false,
+  "description": "Plan the statement with UNKNOWN values for its $1/$2 placeholders - the plan a prepared statement would get. Cannot be combined with `analyze` or `params` (PostgreSQL 16+).",
+  "type": "boolean"
+}
    • addedInput schema / properties / memory
      Added value: +{
+  "default": false,
+  "description": "Report memory used by the planner (PostgreSQL 17+). Works with or without `analyze`, since planning happens either way.",
+  "type": "boolean"
+}
    • changedInput schema / properties / params / items / $ref
      Previous value: -"#/definitions/__schema0"New value: +"#/$defs/__schema0"
    • addedInput schema / properties / serialize
      Added value: +{
+  "description": "Charge the cost of serializing result rows (network-bound queries hide it otherwise). Requires `analyze` (PostgreSQL 17+).",
+  "enum": [
+    "none",
+    "text",
+    "binary"
+  ],
+  "type": "string"
+}
    • addedInput schema / properties / settings
      Added value: +{
+  "default": false,
+  "description": "Report planner GUCs set away from their defaults - explains a weird plan (PostgreSQL 12+).",
+  "type": "boolean"
+}
    • addedInput schema / properties / timing
      Added value: +{
+  "default": true,
+  "description": "Include per-node actual timing. Setting it to false REQUIRES `analyze: true` (it is rejected otherwise, not silently ignored); false lowers measurement overhead.",
+  "type": "boolean"
+}
    • addedInput schema / properties / verbose
      Added value: +{
+  "default": false,
+  "description": "Include output columns, schema-qualified names, and triggers.",
+  "type": "boolean"
+}
    • addedInput schema / properties / wal
      Added value: +{
+  "default": false,
+  "description": "Report WAL generated by the statement. Requires `analyze` (PostgreSQL 13+).",
+  "type": "boolean"
+}
    • changedOutput schema / (root)
      Previous value: -nullNew value: +{
+  "$schema": "https://json-schema.org/draft/2020-12/schema",
+  "additionalProperties": false,
+  "properties": {
+    "plan": {
+      "anyOf": [
+        {
+          "type": "string"
+        },
+        {
+          "items": {},
+          "type": "array"
+        }
+      ],
+      "description": "Newline-joined plan text for `format: \"text\"` (with a trailing truncation marker when POSTGRES_MAX_ROWS chopped it), or the parsed plan array for `format: \"json\"`."
+    }
+  },
+  "required": [
+    "plan"
+  ],
+  "type": "object"
+}
  2. First observedv0.7.0

TDQS

A4.7/5.0
Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

The description discloses far more than the annotations alone: plain EXPLAIN does not execute, ANALYZE does execute, non-SELECT requires ALLOW_WRITES=1, and writes during EXPLAIN ANALYZE are rolled back. It also explains version-gated behavior, explicit version errors instead of parse failures, HypoPG dependency, and teardown of hypothetical indexes. None of this contradicts the annotations, and the destructiveHint is consistent with the fact that ANALYZE actually runs the statement.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is long, but proportionately so: it must explain 14 parameters plus cross-cutting version requirements and side effects. It front-loads the core behavior first, then works through options in a logical order. It loses a point because it is a dense wall of prose in places, and some default information is repeated from the schema rather than relying on the structured field.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a tool with 14 parameters, execution side effects, version constraints, and an output schema, the description is remarkably complete. It covers behavior, security/authorization implications, rollback semantics, extension requirements, and parameter combinations. The presence of an output schema means the description does not need to document return-value structure, and nothing critical is missing for correct invocation.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters5/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The schema already covers 100% of parameters, but the description adds significant meaning on top: default-on behavior for buffers when analyze is true, PostgreSQL version requirements per option, incompatibilities such as generic_plan vs analyze/params, and the semantics of serialize levels. This goes well beyond the schema's standalone property descriptions.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description opens with a precise verb and resource: 'Get the query plan for a SQL statement.' It immediately clarifies the key distinction between plain EXPLAIN and EXPLAIN ANALYZE, which separates this tool from siblings like pg_query or pg_advisor. The raw-SQL-not-prefixed instruction further disambiguates the input contract.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description gives strong option-level usage guidance: when to use buffers ('fastest way to tell a bad plan from a cold cache'), settings ('usual explanation for a plan that looks impossible'), memory ('ask why planning a statement is expensive'), and generic_plan. It also records important constraints such as 'cannot be combined with analyze or params.' However, it never explicitly routes the agent to an alternative sibling for cases where EXPLAIN is not the right tool, so it misses the 'when not to use this tool' part.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Install Server

Other Tools

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/YawLabs/postgres-mcp'

If you have feedback or need assistance with the MCP directory API, please join our Discord server