Advanced Indexing Strategies

Advanced SQL

Index Like a Pro

Indexes are the single most impactful performance tool in SQL. The right index can turn a 30-second query into a 30-millisecond one. But choosing the wrong index wastes space and slows writes.

B-Tree — Default for equality and range queries
GIN — Inverted index for arrays, JSON, full-text search
GiST — Geospatial and range types
BRIN — Block range index for large, naturally ordered tables
Partial — Index only a subset of rows

The key to indexing: understand your query patterns, then create indexes that match them.

Index Types at a Glance

B-Tree Index Fundamentals

-- Standard B-tree index
CREATE INDEX idx_users_email ON users (email);

-- Composite B-tree index (column order matters!)
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

-- Unique constraint (creates unique B-tree index)
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

ℹ️

Key Insight: B-tree indexes support equality and range queries. The column order in composite indexes matters: put equality columns first, then range columns, then sorting columns.

Partial Indexes

-- Index only active users (smaller & faster)
CREATE INDEX idx_active_users ON users (email)
WHERE status = 'active';

-- Index recent orders
CREATE INDEX idx_recent_orders ON orders (customer_id, order_date)
WHERE order_date > CURRENT_DATE - INTERVAL '90 DAY';

-- Index pending tasks
CREATE INDEX idx_pending_tasks ON tasks (assigned_to, priority)
WHERE status = 'pending';

GIN Index for Arrays and JSON

-- GIN index for array containment
CREATE INDEX idx_user_tags ON users USING GIN (tags);

-- GIN index for JSONB
CREATE INDEX idx_user_data ON users USING GIN (data);

-- GIN with specific operator class (smaller, faster)
CREATE INDEX idx_user_data_path ON users USING GIN (data jsonb_path_ops);

GiST Index for Geospatial

-- GiST index for geometric data
CREATE INDEX idx_locations ON stores USING GiST (
  ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
);

-- GiST index for range types
CREATE INDEX idx_booking_dates ON bookings USING GiST (
  tsrange(check_in, check_out)
);

Expression Indexes

-- Index on function result
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Index on computed value
CREATE INDEX idx_orders_total ON orders (
  (quantity * unit_price * (1 - discount))
);

-- Index on date extraction
CREATE INDEX idx_orders_month ON orders (EXTRACT(MONTH FROM order_date));

⚠️

Performance Tip: Expression indexes are only used when the query uses the exact same expression. Make sure your query matches the index definition precisely — LOWER(email) = 'test' uses the index, but email = 'TEST' does not.

Covering Indexes (INCLUDE)

-- Covering index with INCLUDE (avoids table lookups)
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date)
INCLUDE (total_amount, status);

-- Verify index-only scan
EXPLAIN (ANALYZE)
SELECT order_date, total_amount, status
FROM orders
WHERE customer_id = 123;

BRIN Index for Large Tables

-- Block Range Index for time-series data (tiny index size)
CREATE INDEX idx_events_timestamp ON events USING BRIN (event_time);

-- BRIN with custom page range
CREATE INDEX idx_logs_time ON logs USING BRIN (created_at)
WITH (pages_per_range = 32);

Multi-Column Index Strategy

-- Optimal column order:
-- 1. Equality predicates (status = 'active')
-- 2. Range predicates (order_date > '2024-01-01')
-- 3. Sort columns (ORDER BY customer_id)
-- 4. SELECT list columns (INCLUDE for covering)

CREATE INDEX idx_optimal ON orders (
  status,           -- Equality
  order_date,       -- Range
  customer_id       -- Sort
) INCLUDE (total_amount);

Index Maintenance

-- Check index size
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes WHERE tablename = 'employees';

-- Rebuild bloated indexes
REINDEX INDEX idx_users_email;

-- Check index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'employees'
ORDER BY idx_scan DESC;

Concurrent Index Creation

-- Create index without locking table (production-safe)
CREATE INDEX CONCURRENTLY idx_large_table ON large_table (column_name);

-- Check for invalid indexes
SELECT indexrelname, indisvalid, indisready
FROM pg_stat_user_indexes
INNER JOIN pg_index ON indexrelid = pg_stat_user_indexes.indexrelid
WHERE NOT indisvalid;

Quiz: Test Your Knowledge

Follow-Up Questions

When would you choose a GIN index over a B-tree index?
How do partial indexes improve query performance?
Explain the difference between GiST and SP-GiST indexes.
How do you determine the optimal column order for composite indexes?
What's the impact of index maintenance on write performance?

Key Takeaways

B-Tree — Default choice for equality, range, and sort queries
GIN — Best for arrays, JSONB, and full-text search (inverted index)
GiST — Geospatial, range types, nearest-neighbor searches
BRIN — Tiny indexes for large, naturally ordered tables (time-series)
Partial — Index only a subset of rows (WHERE clause) — smaller & faster
Covering — INCLUDE columns to avoid table lookups (index-only scan)
Column order — Equality → Range → Sort → INCLUDE

Advanced Indexing Strategies — B-Tree, GIN, GiST, Partial & Covering Indexes