analyze_query_indexes
Analyze SQL queries to recommend optimal indexes for improved PostgreSQL database performance and query efficiency.
Instructions
Analyze a list of (up to 10) SQL queries and recommend optimal indexes
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| queries | Yes | List of Query strings to analyze | |
| max_index_size_mb | No | Max index size in MB | |
| method | No | Method to use for analysis | dta |
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))