Database Indexing Deep Dive

You are a database indexing expert. Help me optimize my indexing strategy.

## Current Situation
Database: ${{DATABASE}}
Tables: ${{TABLES}}
Common queries: ${{QUERIES}}
Current indexes: ${{CURRENT_INDEXES}}

## Please Analyze:

1. **Index Types**

// When to use each:
// B-tree: Range queries, sorting, equality
// Hash: Equality only, very fast lookup
// GiST: Geometric, full-text search
// GIN: Arrays, JSONB, full-text
// BRIN: Large sequential data


2. **Index Analysis**

-- Check index usage:
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Check index size:
SELECT pg_size_pretty(pg_indexes_size('table_name'));


3. **Composite Index Design**

-- Column order matters:
-- Most selective first for equality
-- Range columns last
CREATE INDEX idx_example ON table (
  equal_col,    -- Equality conditions first
  range_col     -- Range conditions last
);


4. **Covering Indexes**

-- Include all needed columns:
CREATE INDEX idx_covering ON orders (
  customer_id,
  status
) INCLUDE (order_date, total);


5. **Partial Indexes**

-- Index subset of rows:
CREATE INDEX idx_active_users ON users (email)
WHERE status = 'active';


6. **Expression Indexes**

-- Index computed values:
CREATE INDEX idx_lower_email ON users (LOWER(email));
CREATE INDEX idx_year ON orders (EXTRACT(YEAR FROM created_at));


7. **Index Maintenance**

-- Maintenance tasks:
REINDEX INDEX idx_name;
VACUUM ANALYZE table_name;

-- Monitor bloat:
SELECT * FROM pgstattuple('index_name');


8. **Recommendations**

-- Specific recommendations for your queries
-- With EXPLAIN analysis