Postgres by Example: CREATE INDEX

An index is an auxiliary data structure that lets PostgreSQL find rows by a column value without scanning the whole table. A primary key automatically creates one. For other columns, you choose: CREATE INDEX idx ON table (col). The default and most useful index type is B-tree, which speeds up equality (=), range (<, >, BETWEEN), and prefix (LIKE 'abc%') lookups, plus ORDER BY. PostgreSQL also offers GIN (for full-text, arrays, jsonb), GiST (for ranges and geometry), BRIN (for huge, naturally-ordered tables), Hash, and SP-GiST.

What you'll learn:

• Creating a B-tree index on a column or set of columns
• Naming and listing indexes
• Multi-column index order and what it implies
• Partial and expression indexes
• CREATE INDEX CONCURRENTLY for production

-- Single-column B-tree index
CREATE INDEX IF NOT EXISTS fruits_name_idx ON fruits (name);

-- Multi-column index. Order matters!
-- Useful for queries that filter by customer_id, or by (customer_id, id).
CREATE INDEX IF NOT EXISTS orders_customer_idx
  ON orders_example (customer_id, id);

-- Partial index: only index rows matching a predicate. Tiny and fast.
CREATE INDEX IF NOT EXISTS tasks_open_idx
  ON tasks_example (id) WHERE done = false;

-- Expression index: lets queries that use LOWER(email) hit an index.
-- (using a temp table to avoid clutter)
DROP TABLE IF EXISTS people_example;
CREATE TABLE people_example (id integer PRIMARY KEY, email text);
INSERT INTO people_example VALUES (1, 'Alice@example.com'), (2, 'bob@example.com');
CREATE INDEX people_lower_email_idx ON people_example (lower(email));

-- Use it
SELECT * FROM people_example WHERE lower(email) = 'alice@example.com';

-- List indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('fruits', 'orders_example', 'tasks_example', 'people_example')
ORDER BY tablename, indexname;

A B-tree index is a sorted, balanced tree that supports =, <, >, BETWEEN, IS NULL, ORDER BY, and LIKE 'prefix%' (anchored prefix). It is the default and what 90% of indexes in a typical schema are.

Multi-column indexes follow the leftmost prefix rule: an index on (a, b, c) can serve queries that filter on a, on (a, b), on (a, b, c), but not on b alone or (b, c). Put the most selective and most often filtered column first.

Partial indexes index only the rows where a predicate is true. Excellent for "is_active = true" style queries — the index is smaller, faster, and avoids covering most of the (uninteresting) rows. Expression indexes let you index a transformed value (lower(email), (data ->> 'name')). They are the only way to make WHERE lower(email) = 'x' index-fast.

CREATE INDEX CONCURRENTLY builds the index without taking a table-level write lock. It takes longer (two passes over the table) but does not block writes — required in production.

To run (requires earlier tables):

$ psql -f source/create-index.sql postgres
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE INDEX
 id |        email
----+---------------------
  1 | Alice@example.com
(1 row)
 ...

Common pitfalls:

• Over-indexing: every index is updated on every INSERT, UPDATE of an indexed column, and DELETE. On write-heavy tables, indexes you do not actually use are pure overhead.
• Indexing low-cardinality columns alone (e.g. boolean is_active) usually does not help — the planner will sequential-scan anyway. Combine with another column or use a partial index.
• Naming indexes inconsistently makes debugging painful. Adopt a convention like tablename_column_idx.
• CREATE INDEX (without CONCURRENTLY) takes an ACCESS EXCLUSIVE lock and blocks writes for the duration. Always use CONCURRENTLY in production — at the cost of: it cannot run inside a transaction.

Tip: pg_stat_user_indexes shows how often each index is used (idx_scan). Periodically check it; an index with zero scans over weeks is a candidate for dropping.

Try it: Create an index on customer_id only and another on (customer_id, total). Look at EXPLAIN SELECT * FROM orders_example WHERE customer_id = 1 ORDER BY total; and see which one the planner picks. Drop the unused one.

Source: create-index.sql

Next: When to Index

Home: Postgres by Example