postgres-mcp

Instructions

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

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)

• postgres_mcp/postgres_client.py:151-180 (helper)

  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"}