Postgres MCP Pro

Instructions

Implementation Reference

• src/postgres_mcp/server.py:426-450 (handler)

  The analyze_query_indexes tool handler is implemented in src/postgres_mcp/server.py. It registers as an MCP tool, validates the input queries, and utilizes either DatabaseTuningAdvisor or LLMOptimizerTool to generate index recommendations.

  @mcp.tool(description="Analyze a list of (up to 10) SQL queries and recommend optimal indexes")
  @validate_call
  async def analyze_query_indexes(
      queries: list[str] = Field(description="List of Query strings to analyze"),
      max_index_size_mb: int = Field(description="Max index size in MB", default=10000),
      method: Literal["dta", "llm"] = Field(description="Method to use for analysis", default="dta"),
  ) -> ResponseType:
      """Analyze a list of SQL queries and recommend optimal indexes."""
      if len(queries) == 0:
          return format_error_response("Please provide a non-empty list of queries to analyze.")
      if len(queries) > MAX_NUM_INDEX_TUNING_QUERIES:
          return format_error_response(f"Please provide a list of up to {MAX_NUM_INDEX_TUNING_QUERIES} queries to analyze.")
  
      try:
          sql_driver = await get_sql_driver()
          if method == "dta":
              index_tuning = DatabaseTuningAdvisor(sql_driver)
          else:
              index_tuning = LLMOptimizerTool(sql_driver)
          dta_tool = TextPresentation(sql_driver, index_tuning)
          result = await dta_tool.analyze_queries(queries=queries, max_index_size_mb=max_index_size_mb)
          return format_text_response(result)
      except Exception as e:
          logger.error(f"Error analyzing queries: {e}")
          return format_error_response(str(e))