How do I create an index in PostgreSQL?

Run CREATE INDEX idx_orders_customer_id ON orders (customer_id);. PostgreSQL scans the table, sorts the keys, builds a B-tree, and holds a SHARE lock that blocks writes until it finishes. On a live table use CREATE INDEX CONCURRENTLY instead. Then confirm with EXPLAIN (ANALYZE, BUFFERS) that the planner actually chose it.

Key facts

| Fact | Value | |---|---| | Default index method | btree — used when USING is omitted (CREATE INDEX) | | Lock taken by a plain build | SHARE — reads continue, writes block (CREATE INDEX) | | Lock taken by CONCURRENTLY | SHARE UPDATE EXCLUSIVE — writes continue, two table passes (CREATE INDEX) | | Memory used per build | maintenance_work_mem, default 64MB (Resource Consumption) | | Parallel build workers | max_parallel_maintenance_workers, default 2 (Resource Consumption) | | Default B-tree fillfactor | 90 (CREATE INDEX) | | Max columns in one index | 32 by default (CREATE INDEX) | | Duplicate indexes across our fleet | 67.7% of 31 monitored instances fail F3_duplicate_indexes |

Why this happens

The full statement has more parts than most people ever type:

CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] index_name
    ON table_name [USING method]
    ( column_or_expression [COLLATE c] [opclass [(opts)]] [ASC|DESC] [NULLS FIRST|LAST] [, ...] )
    [INCLUDE (column [, ...])]
    [WITH (storage_parameter = value [, ...])]
    [TABLESPACE tablespace_name]
    [WHERE predicate];

Each clause maps to something concrete, and all of them are documented on the CREATE INDEX reference page:

What physically happens during the build

A plain CREATE INDEX takes a SHARE lock on the table. Reads keep working. Every INSERT, UPDATE and DELETE on that table waits until the build finishes. On a small table that is a second. On a hundred-million-row table it is an outage.

CREATE INDEX CONCURRENTLY avoids that by taking only SHARE UPDATE EXCLUSIVE and making two passes over the table, waiting for existing transactions in between. It takes substantially longer, it cannot run inside a transaction block, and if it fails it leaves an invalid index behind that you must drop by hand. The tradeoffs are covered in creating an index concurrently.

Multicolumn indexes and the leftmost prefix

Column order in a multicolumn index is not cosmetic. A B-tree on (a, b, c) can serve predicates on a, on a, b, and on a, b, c. It is far less useful for a query that filters only on b — PostgreSQL can still scan it, but without a bound on the leading column it reads most of the index (Multicolumn Indexes). The practical rule: put the columns used with equality first, the range or sort column last.

-- serves: WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at
CREATE INDEX idx_events_tenant_created ON events (tenant_id, created_at DESC);

Expression indexes must match the query exactly

CREATE INDEX idx_users_lower_email ON users (lower(email));

That index is used by WHERE lower(email) = $1. It is not used by WHERE email = $1, and it is not used by WHERE lower(email) LIKE $1 || '%' under a non-C collation unless you add text_pattern_ops. The planner matches the expression textually after normalisation, so the expression in the query must be the one in the index (Indexes on Expressions). Expression indexes are also more expensive to maintain: the expression is evaluated on every insert and on every update that touches the underlying column.

How to detect it

Start from evidence, not intuition. Tables being read sequentially at volume are the candidates:

SELECT relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

A table with a high seq_tup_read and a low idx_scan is reading a lot of rows to answer something. That is a candidate, not a verdict — a small lookup table read sequentially every time is working exactly as intended, because a sequential scan of eight pages beats an index lookup. Missing indexes and sequential scans goes through the distinction.

Then confirm which predicate is doing the work, and afterwards confirm the index is used:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

Before the index you will see Seq Scan on orders with a row count close to the table size. After it you want Index Scan using idx_orders_customer_id, or Index Only Scan if you added the right INCLUDE columns. If the plan still says Seq Scan, the index is not matching — usually a type mismatch (bigint column, text parameter), an expression that differs from the indexed one, or a table so small the planner is right to ignore it (EXPLAIN). A freshly built index also has no statistics influence until ANALYZE runs, so run it if the plan looks wrong immediately after a build.

To see what already exists, so you do not build the same thing twice:

SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'orders' ORDER BY indexname;

How MyDBA shows this

Tables with the highest sequential-scan counts on this instance - the first place to look before writing a CREATE INDEX.

Missing-index opportunities: the table, the columns the sampled queries filter on, and the CREATE INDEX statement the advisor derived from them.

MyDBA's Index Advisor runs the two queries above continuously and lays the results out as cards: Missing Index Opportunities (subtitled "High seq scan tables"), Unused Indexes, Duplicate Indexes, Low Usage Indexes, Bloated Indexes and Replica-Only Usage ("Used on replicas, not primary"). A missing-index card carries the sampled queries the advice was derived from and the exact DDL, and where the query shape cannot be turned into safe DDL — a casted or wrapped predicate, for example — the advisor shows no SQL rather than SQL that would fail to run. The advisor is cluster-aware: before it will suggest dropping an index it checks usage on every member of the cluster, so an index scanned only by reporting queries on a replica is flagged "Replica-Only" and kept out of the drop list instead of looking unused because the primary never touches it. If you want to see which of your own tables are being scanned sequentially and what the advisor would suggest, start with the free PostgreSQL health check.

How to fix it

1. Confirm the predicate from a real plan. EXPLAIN (ANALYZE, BUFFERS) on the actual query, not a simplified version of it. Index the columns in the WHERE and JOIN clauses that filter the most rows. 2. Choose the column order. Equality columns first, then the range or sort column. Check whether an existing index already has your columns as a leftmost prefix — if (tenant_id, created_at) exists, you do not need (tenant_id). 3. Write the statement, named explicitly and idempotently.

``sql CREATE INDEX IF NOT EXISTS idx_orders_customer_created ON orders (customer_id, created_at DESC); ``

IF NOT EXISTS makes the migration re-runnable, but note it matches on the name only: it will happily skip a differently-defined index that happens to share the name. Never use it with CONCURRENTLY as a retry mechanism, because a failed concurrent build leaves an invalid index that IF NOT EXISTS will then skip forever.

4. Add INCLUDE only if you want an index-only scan.

``sql CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (status, total_amount); ``

Index-only scans also depend on the visibility map, so they need the table to be reasonably well vacuumed (Index-Only Scans).

5. Narrow it with WHERE if most rows are irrelevant.

``sql CREATE INDEX idx_orders_open ON orders (customer_id) WHERE status = 'open'; ``

6. Build it without blocking writes on anything that matters.

``sql CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC); ``

7. Give the build room. maintenance_work_mem defaults to 64MB, which forces an external sort on any large table. Raise it for the session — it is per maintenance operation, so do not set it globally to something your server cannot afford several of at once.

``sql SET maintenance_work_mem = '2GB'; SET max_parallel_maintenance_workers = 4; ``

8. Verify, then analyze. Re-run the EXPLAIN and check for Index Scan using <your index>. Run ANALYZE orders; if the plan has not caught up.

How to prevent it

The more useful question is usually which indexes not to create. Every index is written on every INSERT, on every DELETE, and on every UPDATE that changes an indexed column — and an indexed column change also disqualifies the HOT-update optimisation, so it costs more than the index write alone. Indexes also consume space, memory in the buffer cache, and time on every VACUUM.

The scale of the over-indexing problem shows up plainly in our own monitoring. Across 31 monitored PostgreSQL instances, 67.7% have at least one duplicate index — two indexes covering the same columns in the same order, where one is redundant — and 58.1% carry indexes that have never been scanned. By comparison, only 3.2% of those instances fail the missing-index check. The common failure mode is not a shortage of indexes. It is a pile of them that nobody reads.

Three habits keep that pile from growing:

FAQ

Does CREATE INDEX lock the table?

Yes. A plain CREATE INDEX takes a SHARE lock: concurrent reads are unaffected, but all writes to the table wait for the build to finish. CREATE INDEX CONCURRENTLY takes a weaker SHARE UPDATE EXCLUSIVE lock and allows writes throughout, at the cost of two table passes and a longer total build (CREATE INDEX).

What index name does PostgreSQL choose if I omit one?

It generates <table>_<column(s)>_idx, adding a numeric suffix on collision — so orders (customer_id) becomes orders_customer_id_idx. It is valid but ambiguous once a table has several similar indexes, so naming them explicitly in migrations is worth the extra words.

Why isn't my new index being used?

The usual causes, in order of frequency: a type mismatch between the column and the parameter; a query expression that differs from the indexed expression; a table small enough that a sequential scan genuinely wins; missing statistics right after the build (run ANALYZE); or a partial index whose WHERE clause the planner cannot prove your query implies. Confirm with EXPLAIN (ANALYZE, BUFFERS) rather than guessing.

How do I make an index build faster?

Raise maintenance_work_mem for the session so the sort fits in memory, and raise max_parallel_maintenance_workers (default 2) so B-tree builds use parallel workers (Resource Consumption). Both are per-operation settings, so raise them in the session that runs the build rather than globally. Note that CONCURRENTLY will still be slower than a blocking build, by design.

Should I create the index before or after a bulk load?

After. Building an index once over a finished table is much cheaper than maintaining it through millions of individual row inserts, and the finished index is denser because it is built from a sort rather than grown by page splits.

Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.