list_indexes
Retrieve all indexes for a PostgreSQL table to analyze structure, optimize queries, and manage database performance.
Instructions
List all indexes for a table.
Args:
table_name: Name of the table
schema: Schema name (default: public)
Returns:
List of indexes with name, columns, type, and size
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table_name | Yes | ||
| schema | No | public |
Implementation Reference
- postgres_mcp/server.py:236-266 (handler)MCP tool handler for 'list_indexes'. Decorated with @mcp.tool() for automatic registration. Calls PostgresClient.list_indexes and formats the response dictionary with index details including name, uniqueness, type, size, and definition.@mcp.tool() @handle_db_error def list_indexes(table_name: str, schema: str = "public") -> dict: """List all indexes for a table. Args: table_name: Name of the table schema: Schema name (default: public) Returns: List of indexes with name, columns, type, and size """ client = get_client() indexes = client.list_indexes(table_name, schema) return { "table_name": table_name, "schema": schema, "indexes": [ { "name": idx["index_name"], "is_unique": idx.get("is_unique", False), "is_primary": idx.get("is_primary", False), "type": idx.get("index_type", "btree"), "size": format_bytes(idx.get("size_bytes")), "definition": idx.get("definition", ""), } for idx in indexes ], }
- Core implementation in PostgresClient class. Executes a SQL query joining pg_class, pg_index, pg_namespace, and pg_am to retrieve index details like name, uniqueness, primary status, type, definition, and size for the specified table and schema.def list_indexes(self, table_name: str, schema: str = "public") -> list[dict]: """List indexes for a table. Args: table_name: Table name schema: Schema name Returns: List of index dicts """ query = """ SELECT i.relname AS index_name, t.relname AS table_name, ix.indisunique AS is_unique, ix.indisprimary AS is_primary, am.amname AS index_type, pg_get_indexdef(ix.indexrelid) AS definition, pg_relation_size(i.oid) AS size_bytes FROM pg_class t JOIN pg_index ix ON t.oid = ix.indrelid JOIN pg_class i ON i.oid = ix.indexrelid JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_am am ON am.oid = i.relam WHERE n.nspname = %s AND t.relname = %s ORDER BY i.relname """ with self.get_cursor() as cursor: cursor.execute(query, (schema, table_name)) return [dict(row) for row in cursor.fetchall()]