Skip to content
TheChatArchiveThe Chat Archive
GLMGLM-5 / Pony Alpha· unknownHugging Face Frontier Reasoning Traces

Create a secondary index usage analyzer: dead indexes, duplication, and drop proposals.

  1. User

    Create a secondary index usage analyzer: dead indexes, duplication, and drop proposals.

  2. GLM Assistant

    The user wants a tool or script to analyze secondary index usage in a database. The goal is to identify:

    1. Dead indexes: Indexes that haven't been used for a long time.
    2. Duplication: Indexes that are redundant (e.g., INDEX(A, B) and INDEX(A) where the latter is often covered by the former, or identical definitions).
    3. 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:

    1. Prerequisites: Explain that tracking needs to be enabled (Postgres generally tracks this by default via track_counts, but pg_stat_statements helps).
    2. PostgreSQL Analyzer SQL:
      • Unused/Dead Indexes: Query pg_stat_user_indexes looking for idx_scan = 0 or 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 if index(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.
    3. MySQL Analyzer SQL:
      • Unused: Query sys.schema_unused_indexes.
      • Duplication: Query sys.schema_redundant_indexes.
    4. 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:
      1. Connect.
      2. Run query for unused indexes.
      3. Run query for duplicate indexes.
      4. 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:

    1. Introduction: What we are analyzing.
    2. PostgreSQL Solution:
      • Native SQL queries (for manual analysis).
      • Python Automation Script (The "Analyzer").
    3. MySQL Solution: Brief overview using the sys schema.
    4. 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 = 0 is dangerous if the server was restarted yesterday.
    • Fix: Add a warning or check pg_stat_reset time. I'll note this in the text.
    • Issue: Dropping indexes locks tables in production.
    • Fix: Add CONCURRENTLY to the drop proposals.

    Let's build the Python script to be robust. It should output Markdown or a clear text report.

    Script Logic:

    1. get_unused_indexes: Join pg_stat_user_indexes with pg_index. Filter out indisunique and indisprimary.
    2. get_duplicate_indexes: Use the logic finding overlapping columns.
    3. generate_report: Print the findings and the specific DROP INDEX CONCURRENTLY SQL.

    Let's write the SQL for duplicates carefully. Table pg_index has indkey (array of column attnums). We need to find two indexes i1 and i2 on the same table (indrelid). i1 is redundant if i1.indkey is a prefix of i2.indkey? Actually, usually, the logic is: i1 covers col1. i2 covers col1, col2. i1 is redundant. Or: i1 covers col1. i2 covers col1. 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 DROP commands.

    Prerequisites

    1. Python 3.x installed.
    2. psycopg2 library installed (pip install psycopg2-binary).
    3. PostgreSQL User: Must have read access to pg_stat_user_indexes and pg_index system 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)
    • Logic: Because user_id is 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 CONCURRENTLY commands.

    • Why CONCURRENTLY? In PostgreSQL, standard DROP INDEX locks the table against writes, which can cause downtime in a live application. CONCURRENTLY takes 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.

    1. 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.
    2. 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 sys schema to be installed and performance schema enabled for usage stats).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.