Db Query Optimization

Database Query Optimization

When to Use

• Slow SQL queries identified in application logs
• High database CPU or I/O usage
• After detecting EF Core N+1 or suboptimal queries
• Database performance tuning and index optimization
• Query execution plan analysis

Prerequisites

SQL Server:

• User needs VIEW SERVER STATE permission for DMV access
• Connection configured in .vscode/mcp.json as mssql server

PostgreSQL:

• pg_stat_statements extension recommended (gracefully handle if missing)
• Connection configured in .vscode/mcp.json as postgres server

MCP Server Status:

• Both mssql-dotnet/* and postgres/* tools must be available
• Connections are read-only (no DDL execution)

Analysis Workflow

1. Identify Target Queries

Sources:

• EF Core generated SQL (from /efcore-query-analysis)
• Application logs with slow query warnings
• User-provided SQL query text
• Stored procedure calls (analyzed separately with /stored-proc-analysis)

2. Execute Diagnostic Queries via MCP

For SQL Server (use mssql/* tools)

A. Get Execution Plan for Specific Query

SET SHOWPLAN_TEXT ON;
GO
-- Your query here
SELECT o.OrderId, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
WHERE o.OrderDate > '2024-01-01';
GO
SET SHOWPLAN_TEXT OFF;

Or for actual execution plan with stats:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Your query here

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

B. Find Top Expensive Queries (CPU/Reads/Duration)

SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time_microsec,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_microsec,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC; -- Or total_logical_reads, total_elapsed_time

C. Find Missing Indexes

SELECT
    CONVERT(DECIMAL(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_'
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''), ', ', '_'), '[', ''), ']', '') + '_'
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''), ', ', '_'), '[', ''), ']', '')
        + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL(mid.inequality_columns, '') + ')' 
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT(DECIMAL(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY improvement_measure DESC;

D. Check Index Usage

SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    CASE 
        WHEN s.user_seeks + s.user_scans + s.user_lookups = 0 THEN 'UNUSED'
        WHEN s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) * 10 THEN 'OVER-MAINTAINED'
        ELSE 'ACTIVE'
    END AS index_status
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
    AND i.type_desc <> 'HEAP'
ORDER BY s.user_updates DESC;

E. Check Index Fragmentation

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    CASE 
        WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
        WHEN ips.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE'
        ELSE 'OK'
    END AS recommended_action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 100 -- Ignore small indexes
ORDER BY ips.avg_fragmentation_in_percent DESC;

For PostgreSQL (use postgres/* tools)

A. Get Execution Plan

EXPLAIN ANALYZE
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2024-01-01';

B. Find Top Expensive Queries (requires pg_stat_statements)

-- Check if extension is enabled
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- If enabled, get top queries
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_time_ms,
    min_exec_time AS min_time_ms,
    max_exec_time AS max_time_ms,
    rows / calls AS avg_rows,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

If not enabled, note it as a recommendation.

C. Check Index Usage

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    CASE 
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_scan < 100 THEN 'LOW_USAGE'
        ELSE 'ACTIVE'
    END AS usage_status
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

D. Find Missing Indexes (Seq Scans on Large Tables)

SELECT
    schemaname,
    tablename,
    seq_scan AS sequential_scans,
    seq_tup_read AS rows_read_sequentially,
    idx_scan AS index_scans,
    n_live_tup AS approx_row_count,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    ROUND(100.0 * seq_tup_read / NULLIF(seq_tup_read + idx_tup_fetch, 0), 2) AS seq_scan_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
    AND n_live_tup > 10000 -- Large tables only
    AND seq_tup_read / NULLIF(seq_scan, 0) > 10000 -- Many rows per scan
ORDER BY seq_tup_read DESC
LIMIT 20;

E. Check Table and Index Sizes

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes_size,
    n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

3. Analyze Execution Plans

Look for these red flags:

SQL Server:

• Table Scan / Clustered Index Scan on large tables → missing index
• Key Lookup with high cost → consider covering index
• Sort / Hash Match operators with high cost → missing index on ORDER BY/GROUP BY columns
• Nested Loops with large outer input → consider merge join or hash join via index
• Missing Index warnings in actual execution plan
• High logical reads in STATISTICS IO → table scans or poor indexing

PostgreSQL:

• Seq Scan on large tables (cost >> 1000) → missing index
• Sort with high cost → index on ORDER BY columns
• Hash or Merge Join with low buffer hit ratio → table too large or missing stats
• Nested Loop with large dataset → consider hash/merge join
• High rows removed by filter → predicate not selective enough

4. Generate Index Recommendations

For each problematic query:

1. Identify filter columns (WHERE clause) → equality columns first 2. Identify join columns (ON clause) → must be indexed 3. Identify sort columns (ORDER BY, GROUP BY) 4. Identify covering columns (SELECT columns not in index)

Use index-strategies.md for detailed index design patterns.

Recommendation format:

-- Recommended index for query on Orders table
-- Filters: OrderDate > '2024-01-01' AND Status = 'Pending'
-- Join: CustomerId = c.Id
-- Sort: OrderDate DESC

CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate_CustomerId
ON Orders (Status, OrderDate, CustomerId)
INCLUDE (OrderTotal, ShippingAddress);

-- Estimated Impact: 
--   Table Scan (cost 5000) → Index Seek (cost 50)
--   Reduces logical reads from 10,000 to 100

5. Detect Query Anti-Patterns

Common Issues:

• *SELECT in application queries** → fetch only needed columns
• Functions on indexed columns (e.g., WHERE YEAR(OrderDate) = 2024) → prevents index usage
• Fix: WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
• LIKE with leading wildcard (WHERE Name LIKE '%son') → full table scan
• Fix: Reverse index, full-text search, or redesign query
• Implicit type conversions (e.g., WHERE VarcharColumn = 123) → prevents index usage
• Fix: Match data types in comparison
• OR conditions on different columns → can't use single index efficiently
• Fix: UNION queries or composite index
• Parameter sniffing (SQL Server) → cached plan not optimal for current parameters
• Fix: OPTION (RECOMPILE), OPTIMIZE FOR, or plan guides

6. Output Format

### Query Performance Analysis: [QueryName or Location]

**Current Performance**:
- Execution time: 2.5 seconds
- Logical reads: 50,000
- Execution plan: Table Scan on Orders (500,000 rows)

**Execution Plan Analysis**:
\`\`\`
|--Table Scan (Orders)
   Cost: 4876.23
   Rows: 500,000
   Predicate: OrderDate > '2024-01-01'
\`\`\`

**Problem**: Table scan on large Orders table due to missing index on OrderDate.

**Recommended Index**:
\`\`\`sql
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON Orders (OrderDate DESC)
INCLUDE (OrderId, CustomerId, OrderTotal);
\`\`\`

**Estimated Improvement**:
- Execution time: 2.5s → 0.05s (50x faster)
- Logical reads: 50,000 → 100 (500x reduction)
- Execution plan: Index Seek (cost ~10)

**Additional Recommendations**:
- Consider partitioning Orders table by OrderDate if > 10M rows
- Update statistics on Orders table weekly
- Review query: fetch only required columns instead of SELECT *

Read-Only Enforcement

YOU MUST NOT execute DDL via MCP:

• ❌ CREATE INDEX
• ❌ ALTER TABLE
• ❌ DROP INDEX
• ❌ UPDATE STATISTICS

Only recommend these changes in your findings. Let the user execute them manually after review.

You CAN execute (read-only):

• ✅ EXPLAIN / SET SHOWPLAN_TEXT
• ✅ SELECT from DMVs (sys.dm_*)
• ✅ SELECT from pg_stat_* views
• ✅ SET STATISTICS IO/TIME (read-only)

Integration with Other Skills

• After /efcore-query-analysis: Analyze the generated SQL queries
• Before recommending indexes: Check existing indexes aren't already covering the need
• After analysis: Feed findings into /perf-report-generator

Notes

• Always check for existing indexes before recommending new ones
• Consider index maintenance overhead (writes become slower)
• Large batch operations may benefit from dropping/rebuilding indexes
• Test index recommendations in non-production environment first
• Monitor index usage after creation (unused indexes should be dropped)

Index Strategies and Optimization Patterns

Comprehensive guide to index design, covering both SQL Server and PostgreSQL.

---

Index Design Principles

1. Column Order Matters

Rule: Equality columns first, then range columns, then sort columns.

SQL Server Example:

-- Query:
SELECT OrderId, CustomerName, OrderTotal
FROM Orders
WHERE Status = 'Pending'      -- Equality
    AND OrderDate > '2024-01-01'  -- Range
ORDER BY OrderDate DESC;      -- Sort

-- Optimal index:
CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate
ON Orders (Status, OrderDate DESC) -- Equality first, then range with sort direction
INCLUDE (OrderId, CustomerName, OrderTotal); -- Covering columns

Why:

• Equality columns narrow down the search space first (most selective)
• Range columns can use the sorted nature of the index
• Sort direction in index eliminates separate sort operation

2. Covering Indexes (Include Columns)

SQL Server:

-- Without covering index (Key Lookup required)
CREATE INDEX IX_Orders_Status ON Orders (Status);

-- With covering index (no Key Lookup)
CREATE INDEX IX_Orders_Status_Covering
ON Orders (Status)
INCLUDE (OrderDate, CustomerName, OrderTotal);

PostgreSQL (no INCLUDE clause, add to index):

-- Covering index (all columns in index)
CREATE INDEX ix_orders_status_covering
ON orders (status, order_date, customer_name, order_total);

Trade-off: Covering indexes are larger and slower to maintain but eliminate Key Lookups.

3. Selectivity

High Selectivity (good for indexes):

• Unique or near-unique columns (Email, OrderNumber)
• Status fields with many distinct values
• Foreign keys

Low Selectivity (poor for indexes):

• Boolean flags (IsActive, IsDeleted) with 50/50 distribution
• Gender fields with 2-3 values
• Status fields with only 2-3 states

Exception: Low selectivity can work if you're filtering for the rare case:

-- If only 1% of orders are "Cancelled", index on Status helps
WHERE Status = 'Cancelled'

-- But if 50% of orders are "Pending", index doesn't help much
WHERE Status = 'Pending'

---

Common Index Patterns

Pattern 1: Foreign Key Indexes

Always index foreign keys unless table is tiny (<1000 rows).

-- SQL Server
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders (CustomerId);

-- PostgreSQL
CREATE INDEX ix_orders_customer_id
ON orders (customer_id);

Why: JOINs on foreign keys are extremely common. Without index, nested loop joins are slow.

Pattern 2: Filtered Indexes (SQL Server) / Partial Indexes (PostgreSQL)

Use Case: Index only subset of rows that are frequently queried.

SQL Server:

-- Only index active orders
CREATE NONCLUSTERED INDEX IX_Orders_ActiveOnly
ON Orders (OrderDate DESC, CustomerId)
WHERE Status IN ('Pending', 'Processing');

PostgreSQL:

CREATE INDEX ix_orders_active_only
ON orders (order_date DESC, customer_id)
WHERE status IN ('Pending', 'Processing');

Benefits:

• Smaller index (faster seeks, less storage)
• Reduced maintenance overhead (updates to cancelled orders don't touch index)

Pattern 3: Composite Indexes for Multi-Column Filters

Query:

WHERE Country = 'USA' AND State = 'CA' AND City = 'SF'

Index Order (most selective first):

-- Option 1: Selectivity order (if City is most selective)
CREATE INDEX IX_Users_City_State_Country
ON Users (City, State, Country);

-- Option 2: Query order (if all equally selective)
CREATE INDEX IX_Users_Country_State_City
ON Users (Country, State, City);

Rule of Thumb: Order by selectivity (most selective first) unless there's a range or sort involved.

Pattern 4: Index for ORDER BY

Query:

SELECT * FROM Orders
WHERE CustomerId = 123
ORDER BY OrderDate DESC;

Index:

-- SQL Server
CREATE INDEX IX_Orders_CustomerId_OrderDate
ON Orders (CustomerId, OrderDate DESC);

-- PostgreSQL
CREATE INDEX ix_orders_customer_id_order_date
ON orders (customer_id, order_date DESC);

Impact: Eliminates sort operation (can be expensive for large result sets).

Pattern 5: Index for GROUP BY / DISTINCT

Query:

SELECT CustomerId, COUNT(*)
FROM Orders
WHERE OrderDate > '2024-01-01'
GROUP BY CustomerId;

Index:

CREATE INDEX IX_Orders_OrderDate_CustomerId
ON Orders (OrderDate, CustomerId);

Why: Index supports both the filter (OrderDate) and the grouping (CustomerId).

---

SQL Server Specific Patterns

Clustered Index Selection

Best Candidates (in order): 1. Primary Key (if narrow and sequential) 2. Date/Time column for time-series data (Orders.OrderDate) 3. Identity column (auto-increment)

Avoid:

• Wide keys (multiple columns, > 16 bytes)
• Frequently updated columns (causes page splits)
• Random values (GUIDs) → heavy fragmentation

Example:

-- Good: Sequential clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderDate
ON Orders (OrderDate);

-- Bad: Random GUID clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderGuid
ON Orders (OrderGuid); -- Causes fragmentation

Included Columns vs Index Columns

Indexed Columns (key columns):

• Used for seeks and range scans
• Used for sorting
• Stored in all index levels (leaf and intermediate)
• Count toward 16-column limit

Included Columns (INCLUDE):

• Only stored in leaf level
• Not used for seeks or sorts
• Can be wide (e.g., VARCHAR(MAX), large NVARCHAR)
• Don't count toward 16-column limit

Guideline:

• Filters, joins, sorts → key columns
• SELECT projections → INCLUDE columns

Columnstore Indexes for Analytics

Use Case: Large fact tables (millions of rows) with analytical queries (aggregations, scans).

-- Clustered columnstore for data warehouse fact tables
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON FactSales;

-- Nonclustered columnstore for reporting on OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_Orders_Analytics
ON Orders (OrderDate, CustomerId, ProductId, OrderTotal, Quantity);

Benefits:

• 10x compression
• 10-100x faster for aggregations
• Batch mode execution

Trade-offs:

• Not for OLTP (row-level updates slow)
• Requires SQL Server 2016+ (Enterprise for full features)

---

PostgreSQL Specific Patterns

Index Types

1. B-tree (default) - Use for most cases:

CREATE INDEX ix_orders_order_date ON orders (order_date);

2. Hash - Equality only (=), faster than B-tree:

CREATE INDEX ix_users_email_hash ON users USING HASH (email);

Use case: Exact lookups, no range scans.

3. GIN (Generalized Inverted Index) - For array, JSONB, full-text:

-- JSONB column
CREATE INDEX ix_orders_metadata_gin ON orders USING GIN (metadata);

-- Array column
CREATE INDEX ix_posts_tags_gin ON posts USING GIN (tags);

4. GiST (Generalized Search Tree) - For geometric data, ranges:

-- Range types
CREATE INDEX ix_bookings_date_range_gist ON bookings USING GIST (date_range);

5. BRIN (Block Range Index) - For very large tables with natural order:

-- Time-series data (e.g., 100M rows)
CREATE INDEX ix_events_created_at_brin ON events USING BRIN (created_at);

Very small index, works well when data is naturally sorted (append-only logs).

Expression Indexes

Use Case: Function calls in WHERE clause.

Query:

WHERE LOWER(email) = 'user@example.com'

Index:

CREATE INDEX ix_users_email_lower ON users (LOWER(email));

Query:

WHERE date_trunc('day', created_at) = '2024-01-01'

Index:

CREATE INDEX ix_orders_created_day ON orders (date_trunc('day', created_at));

Operator Class (Text Search)

Pattern Matching:

-- For LIKE 'prefix%' queries
CREATE INDEX ix_users_name_text_pattern ON users (name text_pattern_ops);

-- For case-insensitive searches
CREATE INDEX ix_users_email_citext ON users ((email::citext));

---

Index Maintenance

SQL Server

Fragmentation Management:

-- Check fragmentation
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- Rebuild if > 30% fragmented
ALTER INDEX IX_Orders_OrderDate ON Orders REBUILD;

-- Reorganize if 10-30% fragmented
ALTER INDEX IX_Orders_OrderDate ON Orders REORGANIZE;

Update Statistics:

UPDATE STATISTICS Orders WITH FULLSCAN;

PostgreSQL

Vacuum and Analyze:

-- Reclaim space and update statistics
VACUUM ANALYZE orders;

-- Rebuild index (if bloated)
REINDEX INDEX CONCURRENTLY ix_orders_order_date;

Autovacuum (usually sufficient): PostgreSQL has automatic vacuum, but tune if needed:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.1);

---

Anti-Patterns and Pitfalls

1. Over-Indexing

Problem: Every index slows down INSERT/UPDATE/DELETE.

Rule of Thumb:

• No more than 5-7 non-clustered indexes per table (SQL Server)
• No more than 8-10 indexes per table (PostgreSQL)

Solution: Consolidate overlapping indexes.

Bad:

CREATE INDEX IX_Orders_CustomerId ON Orders (CustomerId);
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate);
-- First index is redundant (covered by second)

Good:

-- Single index covers both queries
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate);

2. Indexing Low-Selectivity Columns

Bad:

-- Only 2 distinct values, 50/50 split
CREATE INDEX IX_Users_IsActive ON Users (IsActive);

Most queries return ~50% of table, so full table scan is faster than index seek + lookups.

Better: Filtered index for the rare case:

-- Only 5% of users are inactive
CREATE INDEX IX_Users_Inactive ON Users (IsActive)
WHERE IsActive = 0;

3. Functions on Indexed Columns

Bad:

-- Can't use index on OrderDate
WHERE YEAR(OrderDate) = 2024

Good:

-- Can use index
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

Or use computed column (SQL Server):

ALTER TABLE Orders ADD OrderYear AS YEAR(OrderDate) PERSISTED;
CREATE INDEX IX_Orders_OrderYear ON Orders (OrderYear);

Or expression index (PostgreSQL):

CREATE INDEX ix_orders_order_year ON orders (EXTRACT(YEAR FROM order_date));

4. GUID Primary Keys without NEWSEQUENTIALID

Bad (SQL Server):

-- Random GUIDs cause page splits
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()
);

Better:

-- Sequential GUIDs reduce fragmentation
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID()
);

Best: Use INT IDENTITY for clustered index, GUID as alternate key:

CREATE TABLE Orders (
    OrderId INT IDENTITY PRIMARY KEY,
    OrderGuid UNIQUEIDENTIFIER UNIQUE DEFAULT NEWSEQUENTIALID()
);

---

Index Selection Decision Tree

Is it a foreign key?
├─ Yes → Create index (IX_Table_ForeignKeyColumn)
└─ No → Continue

Is it in WHERE clause?
├─ Yes → Check selectivity
│   ├─ High (> 5% distinct values) → Good candidate
│   └─ Low (< 5% distinct values) → Consider filtered index or skip
└─ No → Continue

Is it in JOIN ON clause?
├─ Yes → Create index (critical for joins)
└─ No → Continue

Is it in ORDER BY or GROUP BY?
├─ Yes → Include in index (after filter columns)
└─ No → Continue

Are there multiple filter columns?
├─ Yes → Composite index (equality first, range second)
└─ No → Single column index

Are there SELECT columns not in index?
├─ Yes → Add as INCLUDE columns (if < 5 columns)
└─ No → Index is complete

---

Monitoring and Validation

Verify Index Usage (SQL Server)

-- Check if index is used
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks + s.user_scans + s.user_lookups AS total_reads,
    s.user_updates AS writes,
    CASE
        WHEN s.user_seeks + s.user_scans + s.user_lookups = 0 THEN 'UNUSED'
        WHEN s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) * 10 THEN 'WRITE-HEAVY'
        ELSE 'ACTIVE'
    END AS status
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) = 'Orders';

Verify Index Usage (PostgreSQL)

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders';

---

Summary Checklist

Before creating an index:

✅ Check if existing index already covers the query ✅ Verify column selectivity (> 5% distinct values) ✅ Consider query frequency (hot path vs cold path) ✅ Estimate index size and maintenance cost ✅ Test with realistic data volume ✅ Monitor index usage after creation ✅ Remove unused indexes after 30 days

Index design is iterative - start conservative, add indexes based on real usage patterns.