Create a secondary index usage analyzer: dead indexes, duplication, and drop proposals.
- User
Create a secondary index usage analyzer: dead indexes, duplication, and drop proposals.
Assistant
The user wants a tool or script to analyze secondary index usage in a database. The goal is to identify:
- Dead indexes: Indexes that haven't been used for a long time.
- Duplication: Indexes that are redundant (e.g.,
INDEX(A, B)andINDEX(A)where the latter is often covered by the former, or identical definitions). - Drop proposals: Actionable SQL commands or recommendations to remove these inefficient indexes.
I need to choose a database system to make this concrete. PostgreSQL is the most common system where this level of introspection is standard and highly requested. MySQL is also common. I will focus on PostgreSQL as the primary example because its system catalogs (
pg_stat_user_indexes,pg_index) are very powerful for this. I will also provide a section or equivalent for MySQL since it's popular.Plan:
- Prerequisites: Explain that tracking needs to be enabled (Postgres generally tracks this by default via
track_counts, butpg_stat_statementshelps). - PostgreSQL Analyzer SQL:
- Unused/Dead Indexes: Query
pg_stat_user_indexeslooking foridx_scan = 0or very low values compared to table scans. - Duplicate/Redundant Indexes: This is harder. I need to look for indexes where the columns are a prefix of another index (e.g.,
index(a)is redundant ifindex(a,b)exists). There is a query for this using array comparison. - Hypothetical Indexes (Optional but cool): Maybe too advanced, stick to the user's specific request.
- Index Bloat (Optional): Stick to usage/duplication first.
- Unused/Dead Indexes: Query
- MySQL Analyzer SQL:
- Unused: Query
sys.schema_unused_indexes. - Duplication: Query
sys.schema_redundant_indexes.
- Unused: Query
- Wrapper Script (Python): To make it a real "Analyzer", a Python script that connects to the DB and prints a report would be better than just raw SQL snippets. It makes the output "Drop Proposals" rather than just data.
Refining the Python Script:
- Library:
psycopg2(standard for Postgres). - Logic:
- Connect.
- Run query for unused indexes.
- Run query for duplicate indexes.
- Format output nicely with SQL commands to
DROP INDEX.
Drafting the SQL Queries (Mental Check):
- Postgres Unused:
SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; -- Caution: Don't drop unique constraints or PKs. - Postgres Duplicates (Redundant):
This is tricky to write from scratch perfectly without a standard snippet. The logic is: Find index A and Index B on same table. Does A's column list equal the start of B's column list?
Standard query logic:
string_to_array(indkey, ' '). If Index A has cols {1, 2} and Index B has cols {1, 2, 3}, A is redundant.
Structure of the Response:
- Introduction: What we are analyzing.
- PostgreSQL Solution:
- Native SQL queries (for manual analysis).
- Python Automation Script (The "Analyzer").
- MySQL Solution: Brief overview using the
sysschema. - Safety Warnings: Never drop Primary Keys, Unique constraints (unless intended), or Foreign Key backing indexes.
Self-Correction during drafting:
- Issue: Simply looking for
idx_scan = 0is dangerous if the server was restarted yesterday. - Fix: Add a warning or check
pg_stat_resettime. I'll note this in the text. - Issue: Dropping indexes locks tables in production.
- Fix: Add
CONCURRENTLYto the drop proposals.
Let's build the Python script to be robust. It should output Markdown or a clear text report.
Script Logic:
get_unused_indexes: Joinpg_stat_user_indexeswithpg_index. Filter outindisuniqueandindisprimary.get_duplicate_indexes: Use the logic finding overlapping columns.generate_report: Print the findings and the specificDROP INDEX CONCURRENTLYSQL.
Let's write the SQL for duplicates carefully. Table
pg_indexhasindkey(array of column attnums). We need to find two indexesi1andi2on the same table (indrelid).i1is redundant ifi1.indkeyis a prefix ofi2.indkey? Actually, usually, the logic is:i1coverscol1.i2coverscol1, col2.i1is redundant. Or:i1coverscol1.i2coverscol1. That's a strict duplicate.I will provide a standard, battle-tested query for finding redundant indexes in Postgres inside the Python script.
Final Polish: Make sure the output distinguishes between "Strict Duplicates" (identical definition) and "Redundant" (prefix overlap).
Let's start coding/writing. This is a comprehensive solution designed for PostgreSQL, as it provides the most robust system statistics for this type of analysis. I have also included a section at the end for MySQL/MariaDB.
The solution consists of a Python analyzer script that connects to the database, calculates usage metrics, identifies logical duplicates, and generates a Markdown report with specific
DROPcommands.Prerequisites
- Python 3.x installed.
psycopg2library installed (pip install psycopg2-binary).- PostgreSQL User: Must have read access to
pg_stat_user_indexesandpg_indexsystem catalogs.
Part 1: The Python Analyzer Script
Save this file as
index_analyzer.py.import psycopg2 from psycopg2.extras import DictCursor from datetime import datetime import os import sys # ========================================== # CONFIGURATION # ========================================== DB_CONFIG = { "host": "localhost", "database": "your_database_name", "user": "your_username", "password": "your_password", "port": 5432 } # Threshold: How many scans constitutes "usage"? # (Set to 1 to include indexes used even once, 0 to ignore scan count entirely) SCAN_THRESHOLD = 0 # ========================================== # SQL QUERIES # ========================================== # 1. Find Unused (Dead) Indexes # Criteria: Never scanned, not enforcing uniqueness, not a Primary Key SQL_UNUSED = """ SELECT schemaname || '.' || relname AS table, indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan as usage_count FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan <= %s AND NOT indisunique AND NOT indisprimary ORDER BY pg_relation_size(indexrelid) DESC; """ # 2. Find Duplicate/Redundant Indexes # Criteria: Two indexes on the same table where Index A's columns are a prefix of Index B's columns SQL_DUPLICATES = """ SELECT pg_size_pretty(pg_relation_size(a.indexrelid)) as redundant_index_size, a.schemaname || '.' || a.relname as table, a.indexrelname as redundant_index, b.indexrelname as better_index, pg_get_indexdef(a.indexrelid) as redundant_def, pg_get_indexdef(b.indexrelid) as better_def FROM pg_stat_user_indexes a JOIN pg_index ai ON a.indexrelid = ai.indexrelid JOIN pg_stat_user_indexes b ON a.relname = b.relname AND a.schemaname = b.schemaname JOIN pg_index bi ON b.indexrelid = bi.indexrelid WHERE a.indexrelid <> b.indexrelid -- Check if columns of A are a prefix of B (A is subset of B) AND array_to_string(ai.indkey, ' ') = substring(array_to_string(bi.indkey, ' '), 1, length(array_to_string(ai.indkey, ' '))) -- Exclude checking against unique indexes if the candidate isn't unique (we want to keep unique constraints usually) -- But if both are non-unique, mark A as redundant. AND NOT (ai.indisunique = FALSE AND bi.indisunique = TRUE) ORDER BY pg_relation_size(a.indexrelid) DESC; """ def get_connection(): return psycopg2.connect(**DB_CONFIG) def analyze_indexes(): print(f"Connecting to database '{DB_CONFIG['database']}'...") try: conn = get_connection() cur = conn.cursor(cursor_factory=DictCursor) report_lines = [] report_lines.append(f"# Index Usage Analysis Report") report_lines.append(f"**Generated:** {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") report_lines.append("---") # --- Section 1: Dead Indexes --- print("Analyzing dead/unused indexes...") cur.execute(SQL_UNUSED, (SCAN_THRESHOLD,)) dead_indexes = cur.fetchall() report_lines.append("## 1. Dead / Unused Indexes") report_lines.append("These indexes have rarely or never been used for scanning (based on `idx_scan`).") report_lines.append("*Note: Ensure the server has been running long enough to gather meaningful stats.*\n") if not dead_indexes: report_lines.append("*No unused indexes found.*\n") else: report_lines.append("| Table | Index Name | Size | Scans | Drop Proposal |") report_lines.append("|---|---|---|---|---|") for row in dead_indexes: drop_cmd = f"DROP INDEX CONCURRENTLY {row['table'].split('.')[0]}.\"{row['index_name']}\";" report_lines.append(f"| {row['table']} | `{row['index_name']}` | {row['index_size']} | {row['usage_count']} | `{drop_cmd}` |") report_lines.append("\n---") # --- Section 2: Redundant/Duplicate Indexes --- print("Analyzing duplicate/redundant indexes...") cur.execute(SQL_DUPLICATES) duplicates = cur.fetchall() report_lines.append("## 2. Redundant / Duplicate Indexes") report_lines.append("These indexes are subsets of larger indexes. Keeping both wastes disk space and slows down INSERTs/UPDATEs.") report_lines.append("*Logic: If columns of Index A are a prefix of Index B, Index A is redundant.*\n") if not duplicates: report_lines.append("*No redundant indexes found.*\n") else: report_lines.append("| Table | Redundant Index | Better Index | Size Wasted | Drop Proposal |") report_lines.append("|---|---|---|---|---|") for row in duplicates: # We construct the drop command drop_cmd = f"DROP INDEX CONCURRENTLY {row['table'].split('.')[0]}.\"{row['redundant_index']}\";" report_lines.append(f"| {row['table']} | `{row['redundant_index']}` | `{row['better_index']}` | {row['redundant_index_size']} | `{drop_cmd}` |") # Output cur.close() conn.close() return "\n".join(report_lines) except Exception as e: return f"Error during analysis: {e}" if __name__ == "__main__": report = analyze_indexes() # Save to file filename = "index_analysis_report.md" with open(filename, "w") as f: f.write(report) print(report) print(f"\n\n[INFO] Report saved to {filename}")
Part 2: Understanding the Logic
1. Dead Index Detection
The script looks at
pg_stat_user_indexes.- Metric:
idx_scan(Number of times the index has been used). - Filter:
- Ignores Primary Keys (essential for data structure).
- Ignores Unique Constraints (essential for data integrity, even if not used for lookups).
- Identifies indexes with 0 (or very low) usage.
2. Duplication Detection
This detects "Hidden" redundancy.
- Example:
- Index A:
(user_id) - Index B:
(user_id, created_at)
- Index A:
- Logic: Because
user_idis the left-most column in Index B, Index B can satisfy any query that Index A could. Therefore, Index A is redundant waste.
3. Drop Proposals
The script generates
DROP INDEX CONCURRENTLYcommands.- Why
CONCURRENTLY? In PostgreSQL, standardDROP INDEXlocks the table against writes, which can cause downtime in a live application.CONCURRENTLYtakes longer but allows the database to keep operating normally.
Part 3: How to Interpret the Report
After running the script (
python index_analyzer.py), open the generated Markdown file.- Dead Indexes: Look at the size. If you have a 5GB index that has never been scanned, that is free storage space you can reclaim.
- Redundant Indexes: Look at the "Better Index" column.
- Warning: If the "Redundant Index" is a Unique Index and the "Better Index" is Non-Unique, DO NOT DROP IT. The script tries to filter this, but verify manually. Dropping a unique index removes data integrity constraints.
Part 4: For MySQL / MariaDB Users
If you are using MySQL, the system tables are different. You can run this SQL query directly to find unused indexes:
SELECT s.table_schema, s.table_name, s.index_name, s.non_unique, t.engine FROM information_schema.statistics s JOIN information_schema.tables t ON s.table_schema = t.table_schema AND s.table_name = t.table_name LEFT JOIN sys.schema_unused_indexes u ON u.object_schema = s.table_schema AND u.object_name = s.table_name AND u.index_name = s.index_name WHERE s.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema') AND t.engine = 'InnoDB' AND s.index_name != 'PRIMARY' AND u.index_name IS NOT NULL GROUP BY s.table_schema, s.table_name, s.index_name, s.non_unique, t.engine;(Note: MySQL requires the
sysschema to be installed and performance schema enabled for usage stats).
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.