Verify a Database Index Beyond Planner Behavior

This guide shows you how to confirm that a database index actually exists, is structured correctly, and is being used by queries — independent of what the query planner tells you. You’ll learn direct system catalog checks, index usage statistics, and physical verification methods that work across major relational databases. No prior knowledge beyond basic SQL is assumed.

What You’ll Need

• Access to the database with sufficient privileges (typically SELECT on system catalogs and pg_stat_user_indexes or equivalent).
• A SQL client or terminal to run queries.
• Knowledge of the table and index names you want to verify.

Step-by-Step

1. Check the system catalog directly. The most reliable way to verify an index exists is to query the database’s metadata tables. For PostgreSQL, run:

   SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table';

   For MySQL/MariaDB:

   SHOW INDEX FROM your_table;

   For SQL Server:

   SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('your_table');

   This confirms the index’s name, columns, and type (e.g., B-tree, hash) without relying on the planner.

2. Verify the index is actually used. Even if the index exists, the planner may ignore it. Check index usage statistics. PostgreSQL:

   SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'your_table';

   A non-zero idx_scan means the index has been used. MySQL: SHOW STATUS LIKE 'Handler_read_first'; and Handler_read_key — if these increase after running a query, the index is being used. SQL Server: sys.dm_db_index_usage_stats shows seeks, scans, and lookups.

3. Run a query with forced index usage. Temporarily disable sequential scans (PostgreSQL: SET enable_seqscan = off;) or use index hints (MySQL: FORCE INDEX (index_name), SQL Server: WITH (INDEX(index_name))) to see if the query still returns correct results. If it does, the index is valid and usable.
4. Inspect the index structure physically. Use database-specific commands to see the actual index pages. PostgreSQL: CREATE EXTENSION pageinspect; then SELECT * FROM bt_page_stats('index_name', 1);. MySQL: innodb_index_stats table. SQL Server: DBCC SHOWCONTIG (legacy) or sys.dm_db_index_physical_stats. This confirms the index has allocated pages and is not empty or corrupt.
5. Check for index corruption. Run a consistency check. PostgreSQL: REINDEX INDEX index_name; (or REINDEX TABLE). MySQL: CHECK TABLE your_table;. SQL Server: DBCC CHECKTABLE('your_table'). If the check reports no errors, the index is physically sound.
6. Verify index column order and included columns. The index may exist but not match your query’s WHERE clause. Compare the index definition with your query’s predicates. For composite indexes, the leading column must be used for the index to be effective. Use the catalog queries from step 1 to see the column order.
7. Test with a known query plan (but don’t rely on it). Use EXPLAIN to see if the planner chooses the index. If it does, that’s a good sign, but the real proof is the statistics and physical checks above. If the planner doesn’t use it, investigate why — often due to low selectivity, data distribution, or outdated statistics.

Common Mistakes

• Assuming the index is used just because it exists. Many developers create an index and never verify usage. Always check idx_scan or equivalent.
• Checking the wrong table schema. In PostgreSQL, if you have multiple schemas, you must specify the schema in the query. Forgetting it can lead to checking the wrong table.
• Ignoring index size. A very large index may be inefficient. Check its size and compare with the table size — if the index is larger than the table, it may be a sign of poor design.
• Not considering partial or expression indexes. These may not be visible in simple catalog queries. Use pg_indexes with indexdef to see the full definition.
• Relying solely on EXPLAIN. The planner can be wrong due to stale statistics. Always cross-check with actual usage stats.

Tips

• Run ANALYZE (PostgreSQL, MySQL) or UPDATE STATISTICS (SQL Server) before checking planner behavior to ensure statistics are current.
• Use pg_stat_user_indexes over time — a single query may not show usage if the index is rarely used. Monitor for a few days.
• For MySQL, SHOW INDEX also shows cardinality, which indicates how many unique values the index has. Low cardinality means the index may not be helpful.
• In SQL Server, sys.dm_db_index_usage_stats resets on server restart, so check it after a period of activity.
• If you’re verifying an index on a large table, be careful with REINDEX or CHECK TABLE — they can lock the table. Do it during maintenance windows.

Troubleshooting

Index exists but is never used. Check the query’s WHERE clause — if it uses a function on the column (e.g., WHERE UPPER(name) = 'X'), a regular index won’t help. Create a functional index. Also check data distribution — if the column has many duplicates, the planner may prefer a full scan.

Index appears corrupt. Run REINDEX or CHECK TABLE. If that fails, drop and recreate the index. If the table is large, this may take time — plan accordingly.

Index usage stats show zero scans. This could mean the index was just created, or the queries are using a different index. Run a query that should use the index and re-check stats. If still zero, the planner may be choosing a different path — use EXPLAIN to see why.

Catalog query returns no rows. Double-check the table name and schema. In PostgreSQL, use current_schema() to see your active schema. In MySQL, ensure you’re using the correct database with USE dbname;.

Index is there but queries are slow. The index may be fragmented. Rebuild it (PostgreSQL: REINDEX, MySQL: ALTER TABLE ... DROP INDEX ... ADD INDEX, SQL Server: ALTER INDEX ... REBUILD). Also check if the index covers all columns needed — consider adding included columns to avoid table lookups.

Frequently Asked Questions

Q: Can I verify an index without any privileges? No, you need at least read access to system catalogs. Most databases allow any user to see their own tables’ indexes, but some require specific grants.

Q: How often should I check index usage? For production databases, monitor weekly or monthly. If you’re tuning a specific query, check after running it.

Q: What’s the difference between idx_scan and idx_tup_read? idx_scan counts how many times the index was scanned, while idx_tup_read counts how many rows were fetched from the index. A high scan count with low tuple reads might indicate inefficient usage.

Q: Does the planner always use the best index? No, the planner uses statistics and cost estimates. It can be wrong, especially with outdated statistics or complex queries. That’s why direct verification is important.

Q: Can I verify an index on a replica or read-only database? Yes, you can query system catalogs and usage stats on replicas, but note that usage stats may not be replicated (they are local to each instance). Physical checks like REINDEX are not possible on read-only replicas.

Q: Is there a way to see the actual index size? Yes. PostgreSQL: pg_relation_size('index_name'). MySQL: information_schema.tables with DATA_LENGTH for the index. SQL Server: sys.dm_db_partition_stats.

Related

• How to Monitor GitHub for Repository Changes
• Use iPhone Accessibility Features? Full Guide
• Blu-ray Disc: Definition, Formats, and How It Works