On this page
Goal: speed equality, range, ordering, and time-window access; enforce uniqueness.
Create#
CREATE INDEX orders_user_idx ON orders (user_id);
CREATE UNIQUE INDEX users_name_uidx ON users (name);
CREATE INDEX events_ts_idx ON events (ts);
CREATE INDEX events_payload_idx ON events ((payload->>'team'));
-- expression key, single expr onlySyntax: CREATE [UNIQUE] INDEX [IF NOT EXISTS] name ON table [USING method] (col[,…] | (expr)). One optional opclass after a single column (e.g. jsonb_path_ops-style) is accepted. For relational columns, USING btree (the default when omitted) and USING art are both accepted and enforce the same uniqueness and ordering guarantees. For vector columns, USING is required and selects the search method — see Vector indexes.
When to use each#
| Pattern | Index helps |
|---|---|
WHERE user_id = 5 |
equality (B-tree lookup, ART coordination) |
WHERE ts >= … AND ts < …, ORDER BY ts |
range + ordering |
Low-cardinality WHERE status = 'x' |
yes, but still a scan over matches |
PRIMARY KEY / UNIQUE |
durable enforcement (B-tree authority) |
LIKE '%x%', arbitrary expressions |
no (except indexed expression form above) |
Reason about them#
EXPLAIN SELECT * FROM orders WHERE user_id = 5;v0.1.0 EXPLAIN reports Seq Scan … (cost … rows …) shapes; it does not expose a cost-based index-choice trace (optimizer crate is foundations only). Verify wins by measuring (benchmark run, EXPLAIN ANALYZE actual rows).
Constraints#
- Generation-bound lifecycle: rebuild/publish/GC go through
IndexGenerationStore; stale generations are GC'd. - In-memory ART has no persistence/WAL/locks — the persistent B-tree is the authority after restart.
Reference: Indexes · Next: Inspect a query