Indexes and Query Performance

Understand B-tree and specialized indexes, read query plans, and tune queries based on evidence.

Index Fundamentals and B-tree

An index is a separate data structure that helps PostgreSQL locate rows without scanning every row in a table. B-tree is the default and supports common equality and range comparisons, as well as ordered access. Indexes improve reads but consume storage and add work to inserts, updates, and deletes.

Create indexes based on actual query patterns, especially frequently filtered, joined, or sorted columns. A foreign key declaration does not automatically create an index on the referencing column, so evaluate whether one is needed. Avoid indexing every column by default; too many indexes can hurt write performance and increase maintenance cost.

Create an index

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

SELECT id, total
FROM orders
WHERE customer_id = 101;

💡 The index may help find orders for a customer. The planner decides whether to use it based on table size, selectivity, statistics, and estimated cost; an index is not guaranteed to be chosen for every query.

Key Takeaways

Indexes trade storage and write cost for faster access paths.
B-tree is a versatile default index type.
Create indexes based on workload evidence, not every column.

Knowledge Check

Which is a common cost of adding an index?

Composite, Partial, Expression, and Covering Indexes

A composite index stores keys in a defined order; leading-column order matters for many query patterns. A partial index includes only rows matching a predicate, which can be effective for selective subsets such as active records. An expression index indexes a computed expression, such as lower(email), enabling matching case-insensitive searches. INCLUDE can store non-key columns for potential index-only scans.

Indexes should match real predicates and ordering. For example, an index on (customer_id, created_at DESC) may help a query that filters by customer and sorts that customer's records by creation time. It may not provide the same benefit for a query that filters only by created_at.

Composite and partial indexes

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

CREATE INDEX idx_users_lower_email
ON users (lower(email));

CREATE INDEX idx_active_jobs
ON jobs (created_at)
WHERE status = 'active';

💡 Each index supports a different pattern: customer-specific chronological access, case-insensitive email lookup, and queries restricted to active jobs.

Key Takeaways

Composite index column order matters.
Partial indexes can reduce index size for selective predicates.
Expression indexes can support searches on computed values.

Knowledge Check

Which index is designed to include only rows matching a predicate?

EXPLAIN, EXPLAIN ANALYZE, and Statistics

EXPLAIN shows the planner's estimated execution plan. EXPLAIN ANALYZE executes the statement and reports actual timing and row counts alongside estimates. Use it to inspect sequential scans, index scans, join strategies, sorts, and estimated-versus-actual row counts. Remember that EXPLAIN ANALYZE really executes the query; for data-changing statements, it can change data unless wrapped in a transaction and rolled back.

Planner statistics are maintained by ANALYZE and autovacuum. A poor plan may reflect inaccurate statistics, skewed data, missing indexes, an unsuitable query, or a genuinely cheaper sequential scan. A sequential scan is not inherently bad, especially when a query needs much of the table.

Inspect a query plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 101;

💡 The plan includes actual execution information and buffer usage. Compare estimated rows with actual rows and inspect where time and I/O are spent before changing indexes or SQL.

Key Takeaways

EXPLAIN shows estimated plans; EXPLAIN ANALYZE executes the query.
Compare estimated and actual row counts.
A sequential scan can be the best plan for broad queries.

Knowledge Check

What is the key caution when using EXPLAIN ANALYZE on a data-changing statement?

Index Fundamentals and B-tree

Composite, Partial, Expression, and Covering Indexes

EXPLAIN, EXPLAIN ANALYZE, and Statistics

STAY UPDATED WITH BYTES!