# CREATE INDEX

> Create plain, multicolumn, partial, and expression indexes with appropriate locking.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="43.1" caption="CREATE INDEX"}
-- 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 {#run}

To run (requires earlier tables):

```text
$ 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 {#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.

> [!TIP] 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.

> [!NOTE] 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.

## Downloads {#downloads}

SQL file: [create-index.sql](/examples/source/create-index.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)