manage_hypothetical_indexes
Test PostgreSQL index performance by creating hypothetical indexes in memory to analyze query plans without building physical indexes, enabling performance optimization through what-if analysis.
Instructions
Manage HypoPG hypothetical indexes for testing.
HypoPG allows you to create "hypothetical" indexes that exist only in memory and can be used to test query plans without the overhead of creating real indexes.
Actions:
create: Create a new hypothetical index by specifying table and columns
list: List all current hypothetical indexes
drop: Drop a specific hypothetical index
reset: Drop all hypothetical indexes
estimate_size: Estimate the size of a hypothetical index
check: Check HypoPG extension status and availability
hide: Hide an existing real index from the query planner (useful for testing what-if scenarios)
unhide: Unhide a previously hidden index
list_hidden: List all currently hidden indexes
explain_with_index: Create a hypothetical index and explain a query with before/after comparison
This is useful for:
Testing if an index would improve a query
Comparing different index strategies
Estimating index storage requirements
Testing query performance without specific existing indexes (hide)
Simulating index removal scenarios
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| action | Yes | Action to perform | |
| table | No | Table name (required for create, estimate_size, explain_with_index) | |
| columns | No | Column names for the index (required for create, estimate_size, explain_with_index) | |
| index_type | No | Type of index to create | btree |
| unique | No | Whether the index should be unique | |
| index_id | No | Index OID (required for drop, hide, unhide) | |
| query | No | SQL query to explain (required for explain_with_index) | |
| schema | No | Schema name for the table (optional, for create and explain_with_index) | |
| where | No | Partial index WHERE condition (optional, for create) | |
| include | No | Columns to include in INCLUDE clause (optional, for create) |
Implementation Reference
- Core handler function that implements the tool logic for managing hypothetical indexes. Dispatches to different actions like create, list, drop, reset, etc., using HypoPGService.async def run_tool(self, arguments: dict[str, Any]) -> Sequence[TextContent]: try: action = arguments.get("action") if action == "check": status = await self.hypopg_service.check_status() return self.format_json_result({ "hypopg_available": status.is_installed, "hypopg_version": status.version, "message": status.message }) if action == "create": self.validate_required_args(arguments, ["table", "columns"]) hypo_index = await self.hypopg_service.create_index( table=arguments["table"], columns=arguments["columns"], using=arguments.get("index_type", "btree"), schema=arguments.get("schema"), where=arguments.get("where"), include=arguments.get("include"), ) return self.format_json_result({ "success": True, "index_oid": hypo_index.indexrelid, "index_name": hypo_index.index_name, "table": hypo_index.table_name, "schema": hypo_index.schema_name, "definition": hypo_index.definition, "estimated_size_bytes": hypo_index.estimated_size }) elif action == "list": indexes = await self.hypopg_service.list_indexes() return self.format_json_result({ "count": len(indexes), "hypothetical_indexes": [ { "index_oid": idx.indexrelid, "index_name": idx.index_name, "schema_name": idx.schema_name, "table_name": idx.table_name, "access_method": idx.am_name, "definition": idx.definition, "estimated_size_bytes": idx.estimated_size } for idx in indexes ] }) elif action == "drop": self.validate_required_args(arguments, ["index_id"]) success = await self.hypopg_service.drop_index( arguments["index_id"] ) return self.format_json_result({ "success": success, "dropped_index_id": arguments["index_id"] }) elif action == "reset": success = await self.hypopg_service.reset() return self.format_json_result({ "success": success, "message": "All hypothetical indexes have been removed" if success else "Failed to reset hypothetical indexes" }) elif action == "estimate_size": self.validate_required_args(arguments, ["table", "columns"]) # Create temporarily to get size estimate hypo_index = await self.hypopg_service.create_index( table=arguments["table"], columns=arguments["columns"], using=arguments.get("index_type", "btree"), ) size = hypo_index.estimated_size # Clean up await self.hypopg_service.drop_index(hypo_index.indexrelid) return self.format_json_result({ "table": arguments["table"], "columns": arguments["columns"], "index_type": arguments.get("index_type", "btree"), "estimated_size_bytes": size }) elif action == "hide": self.validate_required_args(arguments, ["index_id"]) success = await self.hypopg_service.hide_index( arguments["index_id"] ) return self.format_json_result({ "success": success, "hidden_index_id": arguments["index_id"], "message": "Index is now hidden from the query planner" if success else "Failed to hide index" }) elif action == "unhide": self.validate_required_args(arguments, ["index_id"]) success = await self.hypopg_service.unhide_index( arguments["index_id"] ) return self.format_json_result({ "success": success, "unhidden_index_id": arguments["index_id"], "message": "Index is now visible to the query planner" if success else "Failed to unhide index" }) elif action == "list_hidden": hidden_indexes = await self.hypopg_service.list_hidden_indexes() return self.format_json_result({ "count": len(hidden_indexes), "hidden_indexes": hidden_indexes }) elif action == "explain_with_index": self.validate_required_args(arguments, ["query", "table", "columns"]) result = await self.hypopg_service.explain_with_hypothetical_index( query=arguments["query"], table=arguments["table"], columns=arguments["columns"], using=arguments.get("index_type", "btree"), ) return self.format_json_result(result) else: return self.format_result(f"Unknown action: {action}") except Exception as e: return self.format_error(e)
- JSON schema defining the input parameters for the tool, including action (required) and parameters for each action like table, columns, index_id, etc.inputSchema={ "type": "object", "properties": { "action": { "type": "string", "enum": [ "create", "list", "drop", "reset", "estimate_size", "check", "hide", "unhide", "list_hidden", "explain_with_index" ], "description": "Action to perform" }, "table": { "type": "string", "description": "Table name (required for create, estimate_size, explain_with_index)" }, "columns": { "type": "array", "items": {"type": "string"}, "description": "Column names for the index (required for create, estimate_size, explain_with_index)" }, "index_type": { "type": "string", "enum": ["btree", "hash", "gin", "gist", "brin"], "default": "btree", "description": "Type of index to create" }, "unique": { "type": "boolean", "default": False, "description": "Whether the index should be unique" }, "index_id": { "type": "integer", "description": "Index OID (required for drop, hide, unhide)" }, "query": { "type": "string", "description": "SQL query to explain (required for explain_with_index)" }, "schema": { "type": "string", "description": "Schema name for the table (optional, for create and explain_with_index)" }, "where": { "type": "string", "description": "Partial index WHERE condition (optional, for create)" }, "include": { "type": "array", "items": {"type": "string"}, "description": "Columns to include in INCLUDE clause (optional, for create)" } }, "required": ["action"] },
- src/pgtuner_mcp/server.py:153-157 (registration)Registers the HypoPGToolHandler instance (for manage_hypothetical_indexes) to the MCP server's tool registry via add_tool_handler.add_tool_handler(IndexAdvisorToolHandler(index_advisor)) add_tool_handler(ExplainQueryToolHandler(driver, hypopg_service)) add_tool_handler(HypoPGToolHandler(hypopg_service)) add_tool_handler(UnusedIndexesToolHandler(driver))