explain_query
Analyze SQL query performance by generating execution plans with cost estimates to optimize PostgreSQL database operations.
Instructions
Get the execution plan for a SQL query (EXPLAIN).
Args:
sql: SQL query to explain
analyze: If true, actually runs the query to get real execution stats
(EXPLAIN ANALYZE). Use with caution on slow queries.
Returns:
Execution plan in JSON format with cost estimates
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| analyze | No |
Implementation Reference
- postgres_mcp/server.py:106-121 (handler)MCP tool handler for 'explain_query', registered via @mcp.tool() decorator. Delegates to PostgresClient's explain_query method.@mcp.tool() @handle_db_error def explain_query(sql: str, analyze: bool = False) -> dict: """Get the execution plan for a SQL query (EXPLAIN). Args: sql: SQL query to explain analyze: If true, actually runs the query to get real execution stats (EXPLAIN ANALYZE). Use with caution on slow queries. Returns: Execution plan in JSON format with cost estimates """ client = get_client() return client.explain_query(sql, analyze=analyze)
- Core implementation of the explain_query functionality in PostgresClient class. Constructs and executes an EXPLAIN (FORMAT JSON) query, optionally with ANALYZE.def explain_query(self, query: str, analyze: bool = False) -> dict[str, Any]: """Get EXPLAIN plan for a query. Args: query: SQL query to explain analyze: Whether to actually run the query (EXPLAIN ANALYZE) Returns: Dict with execution plan """ # Only allow EXPLAIN on SELECT queries validated_query = validate_query(query, allow_write=False) explain_cmd = "EXPLAIN (FORMAT JSON" if analyze: explain_cmd += ", ANALYZE, BUFFERS" explain_cmd += f") {validated_query}" with self.get_cursor() as cursor: cursor.execute(explain_cmd) result = cursor.fetchone() if result: plan = list(result.values())[0] return { "success": True, "plan": plan, } return {"success": False, "error": "No plan returned"}