Step 1: Query Pattern Analysis and EXPLAIN Validation

Before creating indexes, extract the top latency contributors and analyze their execution plans. Use EXPLAIN ANALYZE to identify sequential scans, index scans with high row estimates, and heap fetches.

TypeScript Analysis Script:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DB_URL });

export async function analyzeQueryPlan(query: string, params: any[]) {
  const explainQuery = `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`;
  const result = await pool.query(explainQuery, params);
  const plan = JSON.parse(result.rows[0]['QUERY PLAN'])[0];
  
  // Extract critical metrics
  const metrics = {
    executionTime: plan['Execution Time'],
    totalCost: plan['Total Cost'],
    actualRows: plan['Actual Rows'],
    sharedHitBlocks: plan['Shared Hit Blocks'],
    sharedReadBlocks: plan['Shared Read Blocks'],
    // Recursively find index scans vs seq scans
    scanTypes: extractScanTypes(plan)
  };
  
  console.log('Query Plan Analysis:', JSON.stringify(metrics, null, 2));
  return metrics;
}

function extractScanTypes(node: any): string[] {
  let types = [];
  if (node['Node Type'].includes('Scan')) {
    types.push(node['Node Type']);
  }
  if (node['Plans']) {
    node['Plans'].forEach((child: any) => {
      types = types.concat(extractScanTypes(child));
    });
  }
  return types;
}

Step 2: Design Composite Indexes with Left-Prefix Rule

Indexes must follow the order of operations in the query. Equality predicates should precede range predicates. This maximizes the selectivity of the index scan.

Architecture Decision: For the query WHERE user_id = ? AND created_at > ?, the index must be (user_id, created_at). Reversing this order forces the database to scan all created_at entries for every user_id, negating the index benefit.

Step 3: Implement Covering Indexes

If the query only requires specific columns, add them to the index using the INCLUDE clause (PostgreSQL) or ensure they are part of the index key (MySQL). This enables Index-Only Scans.

Migration Template:

-- BAD: Standard composite index forces heap lookup for status and total_amount
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- GOOD: Covering index eliminates heap lookup
-- PostgreSQL syntax
CREATE INDEX idx_orders_user_created_covering 
ON orders(user_id, created_at) 
INCLUDE (status, total_amount);

-- MySQL syntax (columns must be in key or secondary index covers them)
-- In MySQL, ensure the index contains all selected columns
CREATE INDEX idx_orders_user_created_covering 
ON orders(user_id, created_at, status, total_amount);

Step 4: Partial Indexes for Filtered Data

In tables with status columns where 95% of rows are completed, indexing the entire table is wasteful. Use partial indexes to index only relevant rows, reducing index size and write overhead.

-- Index only active orders
CREATE INDEX idx_orders_active 
ON orders(user_id, created_at) 
WHERE status = 'active';

Step 5: Validation and Monitoring

After deployment, monitor pg_stat_user_indexes or sys.dm_db_index_usage_stats to verify usage. Unused indexes must be dropped to reclaim write performance.

export async function getUnusedIndexes() {
  const query = `
    SELECT schemaname, tablename, indexname, idx_scan 
    FROM pg_stat_user_indexes 
    WHERE idx_scan = 0 
    AND indexrelid NOT IN (
      SELECT conindid FROM pg_constraint
    )
    ORDER BY pg_relation_size(indexrelid) DESC;
  `;
  return pool.query(query);
}

Pitfall Guide

1. Ignoring Write Amplification

Every index increases the cost of INSERT, UPDATE, and DELETE operations. The database must update every index structure and write to the WAL. In write-heavy workloads, excessive indexes can throttle throughput.

• Best Practice: Limit indexes to those justified by query patterns. For append-only logs, consider BRIN indexes or minimal indexing.

2. Wrong Column Order in Composite Indexes

Creating an index on (created_at, user_id) when queries filter by user_id first renders the index ineffective for equality lookups. The database cannot skip the first column.

• Best Practice: Always place high-cardinality equality columns first, followed by range columns.

3. Indexing Low-Cardinality Columns Without Filtering

Indexing a status column with values like active/inactive often results in the planner ignoring the index because a sequential scan is cheaper than fetching scattered rows.

• Best Practice: Use partial indexes for low-cardinality columns or combine them in composite indexes where they appear after high-selectivity columns.

4. Breaking SARGability with Functions

Applying functions to indexed columns in the WHERE clause prevents index usage. Example: WHERE YEAR(created_at) = 2023.

• Best Practice: Rewrite queries to use range predicates: WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'. Use expression indexes if function usage is unavoidable.

5. Index Bloat and Fragmentation

Frequent updates and deletes cause index pages to become fragmented, increasing I/O and reducing cache efficiency.

• Best Practice: Schedule regular VACUUM FULL or REINDEX operations during maintenance windows. Monitor bloat ratios via system catalogs.

6. Relying on ORM Auto-Indexing

ORMs often generate indexes for foreign keys and unique constraints automatically. Blindly accepting these can lead to redundant indexes or missing composite opportunities.

• Best Practice: Audit ORM-generated migrations. Override defaults with composite indexes where query patterns demand them.

7. Forgetting Index-Only Scan Opportunities

Developers often stop at creating an index that speeds up the filter but still requires fetching row data. If the table is wide, heap fetches dominate latency.

• Best Practice: Profile queries for Index Only Scan potential. Use INCLUDE columns to cover frequent read paths.

Production Bundle

Action Checklist

• Audit Slow Queries: Extract top 10 latency queries from APM or slow query log.
• Run EXPLAIN ANALYZE: Validate execution plans and identify heap fetches or sequential scans.
• Check Index Usage: Query system stats to identify unused indexes and drop them.
• Design Composite Indexes: Create indexes matching equality-then-range patterns.
• Implement Covering Indexes: Add INCLUDE columns for high-read queries to enable index-only scans.
• Add Partial Indexes: Filter indexes on status or time-based columns to reduce size.
• Validate Write Impact: Benchmark write throughput after index additions; rollback if latency exceeds budget.
• Schedule Maintenance: Configure automated vacuum/reindex jobs based on update frequency.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Read-Heavy Dashboard	Aggressive Covering Indexes	Minimizes I/O, maximizes cache hit rate	High storage, low query cost
High-Write Event Log	Minimal Indexing + BRIN	Reduces write amplification; BRIN handles time-series efficiently	Low storage, high write throughput
JSON Data Queries	GIN Indexes with Operators	Enables efficient indexing of semi-structured data paths	Moderate storage, fast JSON extraction
Multi-Tenant SaaS	Partial Indexes on tenant_id	Isolates index size per tenant; improves selectivity	Balanced storage, tenant isolation
Audit/Compliance Table	Unique Constraints + PK Only	Prevents duplication; reads are rare, writes are append-only	Minimal overhead, data integrity

Configuration Template

PostgreSQL Migration Script:

BEGIN;

-- 1. Drop unused indexes identified by audit
-- DROP INDEX IF EXISTS idx_orders_legacy_status;

-- 2. Create composite covering index for user order history
-- Pattern: SELECT status, total FROM orders WHERE user_id = ? AND created_at > ?
CREATE INDEX CONCURRENTLY idx_orders_user_history_covering 
ON orders (user_id, created_at DESC) 
INCLUDE (status, total_amount);

-- 3. Create partial index for active processing queue
-- Pattern: SELECT * FROM orders WHERE status = 'processing' ORDER BY created_at
CREATE INDEX CONCURRENTLY idx_orders_processing_queue 
ON orders (created_at ASC) 
WHERE status = 'processing';

-- 4. Expression index for case-insensitive search
-- Pattern: SELECT * FROM users WHERE lower(email) = ?
CREATE INDEX CONCURRENTLY idx_users_email_lower 
ON users (lower(email));

-- 5. Verify index creation
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'orders' 
ORDER BY indexname;

COMMIT;

Quick Start Guide

1. Enable Slow Query Logging: Configure your database to log queries exceeding 200ms. In PostgreSQL, set log_min_duration_statement = 200.
2. Identify Top Queries: Analyze logs to find the three most frequent slow queries.
3. Run EXPLAIN: Execute EXPLAIN (ANALYZE, BUFFERS) on these queries. Look for Seq Scan or Index Scan with high Rows Removed by Filter.
4. Create Targeted Index: Based on the WHERE clause, create a composite index. If the query selects few columns, add INCLUDE for those columns.
5. Verify Improvement: Re-run EXPLAIN ANALYZE. Confirm Execution Time has dropped and Shared Read Blocks have decreased. Monitor production metrics for 15 minutes to ensure no write regression.