Working with SQL Indexes
1. Creating Indexes
| Syntax | Effect |
|---|---|
CREATE INDEX idx ON t(col); | Default B-tree index |
CREATE UNIQUE INDEX | Enforces uniqueness |
CREATE INDEX CONCURRENTLY (PG) | Non-blocking build |
USING GIN/GiST/BRIN/Hash | Specialized index types |
DROP INDEX [CONCURRENTLY] | Remove index |
2. Understanding Index Types
| Type | Best For | Notes |
|---|---|---|
| B-tree | Equality, range, ORDER BY | Default; O(log n) lookup |
| Hash | Equality only | Faster than B-tree for = |
| GIN | Arrays, JSONB, full-text | Inverted index |
| GiST | Geometric, ranges, full-text | Generalized search tree |
| BRIN | Very large, naturally ordered | Block-range, tiny size |
| SP-GiST | Non-balanced trees (quadtrees) | Space-partitioned |
| Bitmap (Oracle) | Low-cardinality columns | OLAP workloads |
| Columnstore (SQL Server) | Analytics, compression | Read-heavy |
3. Creating Composite Indexes
| Principle | Description |
|---|---|
| Leftmost Prefix | Index (a,b,c) helps WHERE a / a,b / a,b,c — not just b |
| Most Selective First | Put highest-cardinality column first |
| Equality Before Range | (status, created_at) better than (created_at, status) for status=x AND created_at>y |
| Sort Order | Match ORDER BY direction to avoid sort step |
Example: Composite Index
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, placed_at DESC)
INCLUDE (total); -- covering column4. Using Covering Indexes
| Feature | Behavior |
|---|---|
| Covering Index | All query columns in index → no heap lookup |
| INCLUDE clause | Add non-key columns to leaf pages |
| Index-only scan | EXPLAIN shows "Index Only Scan" |
| Cost | Larger index, slower updates |
5. Creating Partial Indexes
| Use | Example |
|---|---|
| Skip nulls | CREATE INDEX ... WHERE col IS NOT NULL |
| Active rows only | WHERE deleted_at IS NULL |
| Rare values | WHERE status = 'pending' |
| Benefit | Smaller index, faster scans, lower write overhead |
Example: Partial Unique Index
-- Unique only for active users
CREATE UNIQUE INDEX uq_user_email_active
ON users(email) WHERE deleted_at IS NULL;6. Implementing Full-Text Indexes
| DB | Mechanism |
|---|---|
| Postgres | tsvector + GIN; to_tsquery |
| MySQL | FULLTEXT INDEX; MATCH ... AGAINST |
| SQL Server | Full-Text Search service + CONTAINS |
| Beyond DB | Elasticsearch, OpenSearch, Meilisearch for advanced |
Example: Postgres FTS
ALTER TABLE articles ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN(tsv);
SELECT id FROM articles WHERE tsv @@ websearch_to_tsquery('english','database design');7. Analyzing Index Usage
| Tool | Query/Command |
|---|---|
| Postgres usage stats | SELECT * FROM pg_stat_user_indexes; |
| Postgres unused | WHERE idx_scan = 0 |
| MySQL usage | SELECT * FROM sys.schema_unused_indexes; |
| Plan inspection | EXPLAIN (ANALYZE, BUFFERS) ... |
| Missing index | SQL Server DMV sys.dm_db_missing_index_* |
8. Dropping Unused Indexes
| Step | Action |
|---|---|
| Identify | idx_scan = 0 over weeks of representative traffic |
| Confirm not used by unique constraint | Check pg_constraint / information_schema |
| Drop concurrently | DROP INDEX CONCURRENTLY idx_name; |
| Monitor regressions | Watch slow query log after drop |
9. Managing Index Maintenance
| Operation | Why |
|---|---|
| REINDEX (Postgres) | Rebuild bloated indexes |
| ANALYZE | Update planner statistics |
| VACUUM | Reclaim dead tuple space |
| REORGANIZE / REBUILD (SQL Server) | Defrag indexes |
| Optimize TABLE (MySQL) | Rebuild + reclaim space |
10. Balancing Index Overhead vs Query Performance
| Trade-off | Effect |
|---|---|
| More indexes | Faster reads, slower INSERT/UPDATE/DELETE, more disk |
| Wider indexes | Better covering, larger leaf pages |
| Partial indexes | Smaller, less write overhead |
| Index-only scans | Best read perf, demand vacuum for visibility map |
Note: Every index roughly doubles write cost on its column. Audit indexes quarterly.