43 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

Example

-- 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), and can also scan for predicates on b alone or (b, c), though without leading-column restrictions this may require much more of the index. Choose column order from the actual filters and ordering requirements. PostgreSQL 18 adds skip-scan optimization for some of these cases.

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.

Run the example

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: inserts and many updates create index entries; HOT updates can avoid index work, while cleanup of dead index entries is usually deferred to vacuum. 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 a SHARE lock: ordinary reads continue, but writes wait. Use CONCURRENTLY when writes must continue; it cannot run inside a transaction block and has extra work and failure-handling requirements.

Downloads

SQL file: create-index.sql · All lesson files · Setup guide