InstantIndex

Automatically find optimal database indexes using mathematical optimization.

What It Does

Point it at your PostgreSQL database and it will:

1. Analyze your query workload
2. Calculate the mathematically optimal set of indexes
3. Show you exactly what to create and the expected impact
4. Optionally apply the changes automatically

Example

$ instantindex analyze postgresql://localhost/mydb

InstantIndex - Automatic Database Index Optimization

Connecting to database...
✓ Connected

Checking required extensions...
✓ All required extensions available

Analyzing database schema...
✓ Found 6 tables, 81 columns, 6 existing indexes

Analyzing query workload...
✓ Found 9 unique queries (540 total executions)

Generating index candidates...
✓ Generated 26 candidate indexes

Finding optimal index configuration...
Progress |████████████████████████████████████████| 100%

================================================================================
OPTIMIZATION RESULTS
================================================================================

CURRENT STATE:
  Indexes: 6
  Total cost: 1.90M units/day

RECOMMENDED CONFIGURATION:
  Indexes: 14 (8 new)
  Total cost: 176.12K units/day
  Improvement: 90.7% faster
  Storage overhead: 60.80 MB
  Write overhead: +16.0%

NEW INDEXES TO CREATE:

1. CREATE INDEX idx_orders_user_id ON orders (user_id);
2. CREATE INDEX idx_order_items_order_id ON order_items (order_id);
3. CREATE INDEX idx_sessions_user_id ON sessions (user_id);
...

TOP IMPACTED QUERIES:

1. SELECT o.*, oi.*, p.name, p.price FROM orders o JOIN...
   Frequency: 80/day
   Current: 6806ms → Optimized: 48ms
   Speedup: 141.5x (saves 9.0min/day)

2. SELECT * FROM orders WHERE user_id = $1 ORDER BY...
   Frequency: 100/day
   Current: 4260ms → Optimized: 23ms
   Speedup: 181.9x (saves 7.1min/day)

Apply these changes? (y/n):

How It Works

Unlike traditional database tuning which relies on DBA intuition and trial-and-error:

1. Reads your actual query workload from pg_stat_statements
2. Generates index candidates by analyzing WHERE/JOIN/ORDER BY clauses
3. Calculates exact costs using PostgreSQL's query planner (EXPLAIN)
4. Finds optimal configuration using mathematical optimization
5. Recommends the complete index schema that minimizes total cost

This is pure math, not ML or heuristics. PostgreSQL's query planner has deterministic cost functions - we just search the space of possible index configurations to find the global optimum.

Requirements

• PostgreSQL 12+
• pg_stat_statements extension enabled
• hypopg extension (for fast hypothetical index testing)

Installation

npm install -g instantindex

Usage

# Analyze and get recommendations
instantindex analyze postgresql://user:pass@localhost/dbname

# Apply recommendations automatically
instantindex analyze postgresql://localhost/dbname --apply

# Limit storage/write overhead
instantindex analyze postgresql://localhost/dbname --max-storage 500MB --max-write-overhead 5%

Development

npm install
npm run build
npm start -- analyze postgresql://localhost/testdb

Test Database

The project includes a realistic test database setup:

# Create test database with sample e-commerce data
./setup-test-db.sh

# This creates:
# - 50,000 users
# - 10,000 products across 100 categories
# - 200,000 orders with 500,000 order items
# - 2,000 user sessions
# - Realistic query workload (simulated with pg_stat_statements)

# Run optimization on test database
node dist/cli.js analyze postgresql://localhost:5432/instantindex_test

Expected results on test database:

• 90.7% query performance improvement
• 141-538x speedups on individual queries
• 8 recommended indexes
• ~60 MB storage overhead

See EXPERIMENTS.md for detailed analysis of how we achieved these results.