Postgres MCP Pro

Instructions

Implementation Reference

• src/postgres_mcp/server.py:311-377 (handler)

  The `explain_query` function acts as the MCP tool handler. It parses inputs (SQL, analyze flag, hypothetical indexes), validates them, and delegates the core explain logic to the `ExplainPlanTool` class.

  async def explain_query(
      sql: str = Field(description="SQL query to explain"),
      analyze: bool = Field(
          description="When True, actually runs the query to show real execution statistics instead of estimates. "
          "Takes longer but provides more accurate information.",
          default=False,
      ),
      hypothetical_indexes: list[dict[str, Any]] = Field(
          description="""A list of hypothetical indexes to simulate. Each index must be a dictionary with these keys:
      - 'table': The table name to add the index to (e.g., 'users')
      - 'columns': List of column names to include in the index (e.g., ['email'] or ['last_name', 'first_name'])
      - 'using': Optional index method (default: 'btree', other options include 'hash', 'gist', etc.)
  
  Examples: [
      {"table": "users", "columns": ["email"], "using": "btree"},
      {"table": "orders", "columns": ["user_id", "created_at"]}
  ]
  If there is no hypothetical index, you can pass an empty list.""",
          default=[],
      ),
  ) -> ResponseType:
      """
      Explains the execution plan for a SQL query.
  
      Args:
          sql: The SQL query to explain
          analyze: When True, actually runs the query for real statistics
          hypothetical_indexes: Optional list of indexes to simulate
      """
      try:
          sql_driver = await get_sql_driver()
          explain_tool = ExplainPlanTool(sql_driver=sql_driver)
          result: ExplainPlanArtifact | ErrorResult | None = None
  
          # If hypothetical indexes are specified, check for HypoPG extension
          if hypothetical_indexes and len(hypothetical_indexes) > 0:
              if analyze:
                  return format_error_response("Cannot use analyze and hypothetical indexes together")
              try:
                  # Use the common utility function to check if hypopg is installed
                  (
                      is_hypopg_installed,
                      hypopg_message,
                  ) = await check_hypopg_installation_status(sql_driver)
  
                  # If hypopg is not installed, return the message
                  if not is_hypopg_installed:
                      return format_text_response(hypopg_message)
  
                  # HypoPG is installed, proceed with explaining with hypothetical indexes
                  result = await explain_tool.explain_with_hypothetical_indexes(sql, hypothetical_indexes)
              except Exception:
                  raise  # Re-raise the original exception
          elif analyze:
              try:
                  # Use EXPLAIN ANALYZE
                  result = await explain_tool.explain_analyze(sql)
              except Exception:
                  raise  # Re-raise the original exception
          else:
              try:
                  # Use basic EXPLAIN
                  result = await explain_tool.explain(sql)
              except Exception:
                  raise  # Re-raise the original exception
  
          if result and isinstance(result, ExplainPlanArtifact):