Database Index Not Being Used Fix

In this tutorial, you'll learn about Database Index Not Being Used Fix. We cover key concepts, practical examples, and best practices.

You created an index on a column, but EXPLAIN ANALYZE still shows a Sequential Scan — the query planner is not using the index because of a data type mismatch, function wrapping the column, or outdated statistics.

Step-by-Step Fix

1. Run EXPLAIN ANALYZE to verify

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

Expected output if index is NOT used:

Seq Scan on orders  (cost=0.00..5000.00 rows=1 width=40)
  Filter: (customer_id = 12345)

2. Check for type mismatches

-- Wrong — comparing integer column to string value
CREATE INDEX idx_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = '12345';
-- Type mismatch: int4 vs text — index not used!

-- Right — match the column type
SELECT * FROM orders WHERE customer_id = 12345;
-- Index Scan using idx_customer_id

3. Remove function wrappers from columns

-- Wrong — function on column prevents index use
EXPLAIN ANALYZE
SELECT * FROM orders WHERE DATE(order_date) = '2024-01-15';
-- Seq Scan — cannot use index on order_date

-- Right — use comparison without function
SELECT * FROM orders
WHERE order_date >= '2024-01-15'
  AND order_date < '2024-01-16';
-- Index Scan — uses index on order_date

4. Update statistics

-- Wrong — outdated statistics cause planner to choose Seq Scan
ANALYZE orders;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;
-- Still Seq Scan? Try with increased statistics target

-- Right — increase statistics target for better estimates
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

Common Mistakes

Prevention

• Run EXPLAIN ANALYZE after creating every index to verify it is used.
• Use pg_stat_user_indexes to see which indexes are actually used.
• Schedule ANALYZE to update table statistics automatically.
• Avoid functions and type casts on indexed columns in WHERE clauses.
• Monitor unused indexes with pg_stat_all_indexes and remove them.

DodaTech Tools

Doda Browser's database inspector visualizes index usage statistics and suggests index candidates. DodaZIP archives schema definitions and index configurations for disaster recovery. Durga Antivirus Pro maintains minimal indexes on its threat database for optimal write performance.

Common Mistakes with index not used

1. Mixing let bindings with <- bindings in do notation, producing type errors
2. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors
3. Non-exhaustive pattern matches that compile with warnings then crash at runtime

These mistakes appear frequently in real-world DATABASE code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ