Indexes, EXPLAIN & query performance

An index is an additional data structure that helps the engine locate rows without scanning every row. Indexes can transform query performance, but every index consumes storage and adds work to INSERT/UPDATE/DELETE operations.

What an index helps

Indexes are often useful for selective WHERE predicates, join keys, and ORDER BY patterns. A primary key normally creates or uses an index automatically, depending on the engine.

A query such as WHERE customer_id = ? over millions of orders is a typical candidate for an index on orders(customer_id).

Single-column indexes

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Run the query to see the result.

This is static because schema modification is not enabled in learning blocks. In a real database, benchmark the actual workload instead of adding indexes mechanically.

Composite indexes and column order

CREATE INDEX idx_orders_customer_status_date
ON orders(customer_id, status, order_date);

Run the query to see the result.

Column order matters. An index beginning with customer_id can efficiently support many searches constrained by customer_id, but it is not equivalent to a separate index beginning with status.

Covering indexes

If an index contains all columns required by a query, some engines can answer it without reading the base table for each match. This can be fast, but wider indexes consume more space and maintenance work.

Selectivity and low-cardinality columns

An index on a boolean-like or low-cardinality status column may be unhelpful when a large fraction of the table matches. Data distribution matters as much as syntax.

EXPLAIN instead of intuition

PostgreSQL and SQLite provide EXPLAIN facilities that show the chosen plan. PostgreSQL also supports EXPLAIN ANALYZE, which actually executes the query and reports observed timing/row information. Use production-like data and caution with write queries.

Learn to inspect:

• scan type (sequential/table scan versus index access),
• estimated versus actual rows,
• join strategy and join order,
• sort/aggregate steps,
• repeated loops,
• large intermediate result sets.

Performance anti-patterns

Common issues include selecting unnecessary columns, missing join predicates, functions/casts that prevent useful index access, deep OFFSET pagination, N+1 application queries, leading-wildcard searches, and aggregating far more rows than needed.

Practice: explain which index you would consider for “all paid orders for one customer ordered by order_date descending,” and why column order matters.