CREATE INDEX name ON table (column); builds an index that lets PostgreSQL find matching rows without reading the whole table. The default type is B-tree, which serves =, <, >, BETWEEN, IN, IS NULL and ORDER BY. Pick another type with USING: gin, gist, spgist, brin or hash.
The plan's first line reads Index Scan using users_email_idx on users: PostgreSQL looks the value up in the index and reads only the matching row. Remove the CREATE INDEX line and run it again to see Seq Scan on users, which reads all 10,000 rows.
On a table of a few dozen rows, the planner often picks a sequential scan even when an index exists: reading a handful of pages is cheaper. EXPLAIN ANALYZE explains how to read these plans.
Choose an index type
| Type | Use it for |
|---|---|
| B-tree (default) | equality, ranges, sorting, LIKE 'prefix%' (with the C collation or text_pattern_ops) |
| GIN | values with many parts: jsonb (@>, ?), arrays (@>, &&), full-text tsvector |
| GiST | geometry, ranges (&&, overlaps), full-text, nearest-neighbor ORDER BY ... <-> |
| SP-GiST | data that splits into non-overlapping regions: points, IP addresses, text prefixes |
| BRIN | very large tables whose values follow the physical order, like an append-only created_at |
| Hash | equality only; a B-tree does the same and more, so it is rarely chosen |
A GIN index on a jsonb column makes containment queries use the index:
The plan shows a Bitmap Index Scan on the GIN index, feeding a Bitmap Heap Scan that reads the matching rows. The JSONB page covers its operators.
Index several columns
A multicolumn B-tree index sorts by the first column, then by the second within each value of the first. It serves queries that filter on the leading column, or on the leading column and the next ones. A filter on the second column alone gains little from it, so put the column you always filter on with = first.
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
INCLUDE (PostgreSQL 11+) stores extra columns in the index without sorting by them: CREATE INDEX ON orders (customer_id) INCLUDE (total);. A query that reads only those columns can then be answered from the index alone, an Index Only Scan.
Unique and partial indexes: enforce a rule on some rows
CREATE UNIQUE INDEX rejects duplicate values. A partial index adds a WHERE and indexes only the rows that match it. It is smaller, and it can make a uniqueness rule apply to a subset of rows:
Both rows are accepted because only one of them is not deleted. A third live row with the same email fails with duplicate key value violates unique constraint "accounts_email_live". A query uses a partial index only when its WHERE implies the index's condition, here WHERE NOT deleted.
Index an expression
An index can store the result of an expression. A query must repeat the same expression to use it, and an index on email does not help WHERE lower(email) = ...:
The plan uses users_email_lower_idx because the WHERE repeats lower(email) exactly. The expression must be immutable, so now() or a function that depends on a setting is refused.
List, build and drop indexes on a live database
pg_indexes lists every index with the statement that would recreate it:
The primary key and the UNIQUE constraint created their own indexes, users_pkey and users_email_key. In psql, \d users shows the same list under the table's columns.
On a table that receives writes, build and drop indexes with CONCURRENTLY so writes are not blocked. These forms cannot run inside a transaction block. A failed concurrent build leaves an INVALID index, which you must drop and create again:
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx; -- rebuild a bloated index
Frequently Asked Questions
Does PostgreSQL create indexes automatically?
Only for PRIMARY KEY, UNIQUE and exclusion constraints. A foreign key column gets no index, so add one yourself when you join on it or delete rows from the referenced table.
Why is PostgreSQL not using my index?
Common reasons: the table is small, so reading it whole is cheaper; the query returns a large share of the rows; the WHERE wraps the column in a function or cast the index does not have; or the statistics are stale (run ANALYZE). EXPLAIN shows which plan was chosen.
How many indexes are too many?
There is no fixed number. Every index speeds up some reads and slows down every INSERT and most UPDATEs on the table, and it takes disk space. pg_stat_user_indexes.idx_scan shows how often each index is used, so you can drop ones that stay at zero.
Does CREATE INDEX lock the table?
Plain CREATE INDEX blocks INSERT, UPDATE and DELETE on the table until it finishes, though reads continue. CREATE INDEX CONCURRENTLY allows writes during the build, takes longer and cannot run inside a transaction block.