explain_with_indexes
Analyze PostgreSQL query performance by testing hypothetical indexes without creating them. Compare execution plans to identify optimal indexing strategies using the HypoPG extension.
Instructions
Run EXPLAIN on a query, optionally with hypothetical indexes.
This tool allows you to see how a query would perform with proposed indexes WITHOUT actually creating them. Requires HypoPG extension for hypothetical testing.
Use this to:
Compare execution plans with and without specific indexes
Test if a proposed index would be used
Estimate the performance impact of new indexes
Returns both the original and hypothetical execution plans for comparison.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | The SQL query to explain | |
| hypothetical_indexes | No | List of hypothetical indexes to test | |
| analyze | No | Whether to use EXPLAIN ANALYZE (executes the query) |
Input Schema (JSON Schema)
{
"properties": {
"analyze": {
"default": false,
"description": "Whether to use EXPLAIN ANALYZE (executes the query)",
"type": "boolean"
},
"hypothetical_indexes": {
"description": "List of hypothetical indexes to test",
"items": {
"properties": {
"columns": {
"description": "Columns for the index",
"items": {
"type": "string"
},
"type": "array"
},
"index_type": {
"default": "btree",
"enum": [
"btree",
"hash",
"gin",
"gist",
"brin"
],
"type": "string"
},
"table": {
"description": "Table name",
"type": "string"
},
"unique": {
"default": false,
"type": "boolean"
}
},
"required": [
"table",
"columns"
],
"type": "object"
},
"type": "array"
},
"query": {
"description": "The SQL query to explain",
"type": "string"
}
},
"required": [
"query"
],
"type": "object"
}