Chapter 8: Indexes and Performance Basics

Indexes speed up reads by providing a smaller, ordered structure that the query planner can search quickly; they trade extra storage and slower writes for faster lookups. This chapter explains physical organization (clustered versus non-clustered using alternative wording), composite index design and maintenance settings, and practical guidance on when to index or leave data unindexed.

Clustered vs non-clustered

A clustered organization stores table rows in the same order as a chosen key; a non-clustered index stores a separate structure that points to table rows. Different engines implement this differently.

Engine behaviors at a glance

Practical implications

• Range scans: clustered order helps queries that read contiguous key ranges (for example by date).
• Covering lookups: non-clustered indexes can include needed columns to avoid visiting the base table (engine support varies).
• Write cost: maintaining order causes page splits and extra I/O under random insert patterns; bulk loads benefit from pre-sorted input where feasible.

-- SQL Server: create a clustered index on (created_at, id)
CREATE CLUSTERED INDEX cix_orders_created_id
  ON dbo.orders (created_at, id);

-- PostgreSQL: opportunistic clustering (one-off rewrite)
CLUSTER orders USING idx_orders_created_at;
-- Later, to refresh the order again:
CLUSTER orders;

-- MySQL (InnoDB): primary key defines clustering
ALTER TABLE orders
  ADD PRIMARY KEY (created_at, id);

Composite indexes, fillfactor, and maintenance

Composite indexes store multiple columns in a single index key. The column order matters; the index can support lookups that match a left-most prefix of the key (and sometimes more with advanced checks by the planner).

Designing composite keys

• Put equality filters first, then range filters, then columns used for ordering.
• Prefer one well-chosen composite index over several overlapping single-column indexes.
• Use expression or functional indexes for case-insensitive or computed lookups where supported.
• Use include columns to cover queries without widening the key (SQL Server and PostgreSQL have explicit INCLUDE).

-- Composite equality then range
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);

-- Expression index for case-insensitive search
-- PostgreSQL
CREATE INDEX idx_users_email_lower ON users ((lower(email)));
-- SQLite
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- Covering index with include columns
-- PostgreSQL 11+
CREATE INDEX idx_orders_cov ON orders (customer_id, order_date) INCLUDE (status, total);
-- SQL Server
CREATE INDEX idx_orders_cov ON dbo.orders (customer_id, order_date) INCLUDE (status, total);

Fillfactor (page fullness) and fragmentation

Fillfactor controls how full to pack index pages at build time so that future inserts have room to grow. Lower values reduce page splits at the cost of more disk space.

-- PostgreSQL
CREATE INDEX idx_events_created_at
  ON events (created_at)
  WITH (fillfactor = 90);

-- SQL Server
CREATE INDEX idx_events_created_at
  ON dbo.events (created_at)
  WITH (FILLFACTOR = 90, PAD_INDEX = ON);

Index maintenance and statistics

• Rebuild versus reorganize: heavy rebuild recreates the index; light reorg defragments leaf pages where supported.
• Statistics: up-to-date statistics inform the planner; autovacuum or auto-stats usually keep these fresh, but manual refresh can help after big changes.
• Concurrent builds: build or rebuild without long blocking where the engine supports it.

-- PostgreSQL
REINDEX INDEX idx_users_email;          -- full rebuild
ANALYZE users;                          -- refresh stats
CREATE INDEX CONCURRENTLY idx_e_c ON events (created_at);

-- SQL Server
ALTER INDEX ALL ON dbo.orders REORGANIZE;
ALTER INDEX ALL ON dbo.orders REBUILD WITH (ONLINE = ON);  -- edition dependent
UPDATE STATISTICS dbo.orders WITH FULLSCAN;

-- MySQL
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;                  -- can rebuild table and indexes (engine specific)

When (and when not) to index

Not every column should be indexed. Good candidates are columns used in frequent search predicates, joins, and orderings where the index avoids scanning large portions of the table. Poor candidates either do not filter much or change so often that the write cost outweighs the read benefit.

Good candidates

• Foreign key columns used in joins (for example orders.customer_id).
• Columns used in equality filters on large tables (for example WHERE email = ?).
• Composite patterns that match common queries (for example (customer_id, created_at) for recent-by-customer listings).
• Columns used for sorting or range scans (for example ORDER BY created_at DESC LIMIT 50).
• Expressions that normalize search (for example lower(email) or JSON key extraction) where the engine supports expression indexes.

-- Typical lookup and ordering needs
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- Join helper for a foreign key
CREATE INDEX idx_orders_product_id
  ON orders (product_id);

Poor candidates

• Columns with very low cardinality (for example a boolean flag) unless paired in a composite index that increases selectivity.
• Small tables that fit in memory or are scanned quickly.
• Columns updated in nearly every write (for example counters that change on each event).
• Leading wildcard patterns that cannot use a normal b-tree (for example LIKE '%term'); consider full-text features instead.
• Highly volatile temporary data that is created and deleted in short cycles.

Measuring impact

• Record query latency and throughput before and after adding an index.
• Inspect plan changes (for example index-only scans versus heap visits; key lookups versus scans).
• Track write latency and storage growth to catch harmful side effects.

-- PostgreSQL: show why the planner chooses a path
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50;

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (50) *
FROM dbo.orders
WHERE customer_id = @cid
ORDER BY created_at DESC;

With a clear understanding of physical layout, careful composite design, sensible fillfactor choices, and a policy for measuring benefits, you can keep read performance high without over-indexing or harming write throughput.