Create an index

When to index, what to index, and what query patterns indexes serve.

Version
Latest
v0.1.0 · latest 2 min read
On this page
  1. Create
  2. When to use each
  3. Reason about them
  4. Constraints

Goal: speed equality, range, ordering, and time-window access; enforce uniqueness.

Create#

sqlsource
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 only

Syntax: 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#

sqlsource
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

Was this page helpful?