Indexes and Query Performance

As tables grow to millions of rows, some queries that were instant at 100 rows can become painfully slow. Indexes are the primary tool for keeping queries fast.

The analogy: a book index

Without an index, finding a topic in a book means reading every page — a "full table scan." A book's index lets you jump directly to the right page. A database index works the same way for a column's values.

Creating an index

CREATE INDEX idx_employees_department_id ON employees (department_id);

Now, a query filtering on department_id can jump directly to matching rows instead of scanning the entire table.

Primary keys and unique columns are indexed automatically

You never need to manually index a PRIMARY KEY or UNIQUE column — the database creates that index for you automatically, since it has to enforce uniqueness anyway.

Composite (multi-column) indexes

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Useful when you frequently filter on both columns together. Column order matters — this index helps queries filtering by customer_id alone, or by customer_id AND status, but not by status alone.

The tradeoff: indexes aren't free

• They speed up SELECT queries that filter, join, or sort on the indexed column(s).
• They slow down INSERT/UPDATE/DELETE, since every index also has to be updated when the underlying data changes.
• They use additional disk space.

The rule of thumb: index columns you frequently search, filter, or join on — especially foreign keys — but don't index everything "just in case."

Seeing what the database will actually do: EXPLAIN

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- PostgreSQL: adds actual timing
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

EXPLAIN shows the query's execution plan — whether it's using an index or scanning the whole table — the essential tool for diagnosing a slow query.

Dropping an index

DROP INDEX idx_employees_department_id;

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;