Indexes and Query Performance

Why queries are slow, how indexes help and how to read EXPLAIN.

A query that takes 2 ms on a hundred rows can take minutes on a hundred million. Without help, the database must read every row to find matches, a full table scan. An index is a separate, sorted lookup structure (usually a B-tree) that lets it jump straight to the right rows, like the index at the back of a book.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);   -- composite

Primary keys and UNIQUE constraints are indexed automatically. Foreign key columns usually are not, and they are the most common candidates: joins and lookups on them are constant companions.

Reading the plan

EXPLAIN shows how the database intends to run a query. Look for a scan of the whole table versus an index search.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

-- before index: SCAN orders
-- after index:  SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

In PostgreSQL use EXPLAIN ANALYZE to also run the query and report real timings.

The trade-off

• Indexes make reads faster but every INSERT, UPDATE and DELETE must also maintain them, so writes get slower.
• They use disk space. Do not index everything; index columns you filter, join and sort on.
• Low-selectivity columns (such as a boolean) rarely benefit.

Composite index order

An index on (customer_id, status) helps queries filtering by customer_id, or by both, but not by status alone. Put the most commonly filtered, most selective column first.

-- Slow: function on the column prevents index use
SELECT * FROM customers WHERE LOWER(email) = 'a@b.com';

-- Slow: leading wildcard
SELECT * FROM customers WHERE name LIKE '%son';

-- Better: compare the raw column
SELECT * FROM customers WHERE email = 'a@b.com';

Other habits that keep queries fast

• Select only needed columns instead of SELECT *.
• Paginate with LIMIT; for deep pages prefer keyset pagination (WHERE id > last_seen) over big OFFSETs.
• Avoid the N+1 problem: one query per row from application code. Fetch related rows with a join or an IN list.

-- Equality columns first, then the sort column, so the database can
-- read matching rows already in order without a separate sort step.
CREATE INDEX idx_orders_lookup ON orders (customer_id, status, created DESC);