Working with Indexes

1. Creating Index

CREATE INDEX orders_customer_id_idx ON orders(customer_id);
CREATE INDEX orders_placed_at_idx   ON orders(placed_at DESC);

CREATE UNIQUE INDEX customers_email_lower_uidx
   ON customers (lower(email));

CREATE INDEX orders_open_idx ON orders(placed_at)
 WHERE status IN ('new','paid');

Benefit	Detail
Smaller	Indexes only matching rows
Faster lookups	Predicate hot-path
Matching	Query predicate must imply index predicate

CREATE INDEX events_user_id_idx ON events ((payload->>'user_id'));
CREATE INDEX customers_email_lc ON customers ((lower(email)));

CREATE INDEX orders_cust_placed_idx ON orders(customer_id, placed_at DESC);

Note: Leftmost column is mandatory for the index to be used; predicate on second column alone bypasses it.

CREATE INDEX events_session_hash ON events USING hash (session_id);

Property	Detail
Operator	= only
WAL-logged	Yes (since PG 10)
Use case	Large equality-only lookup on long keys

CREATE INDEX reservations_during_gist ON reservations USING gist (during);
CREATE INDEX shops_loc_gist ON shops USING gist (location);

Data Types	Operators
range, geometric, tsvector	@>, &&, <>
PostGIS geometry	Spatial predicates

Data Types	Operators
arrays, jsonb, tsvector, hstore	@>, ?, &&, @@
trigram (pg_trgm)	LIKE/ILIKE, %, <%

CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 32);

Property	Detail
Best for	Append-only, time-ordered fact tables
Size	Tiny (KB for GB tables)
Performance	Range scans only; not point lookups

CREATE INDEX url_spgist ON urls USING spgist (url text_ops);

CREATE INDEX orders_cust_covering
   ON orders(customer_id) INCLUDE (status, total_cents);

Note: INCLUDE columns aren't in the search key but enable index-only scans for additional projected columns.

CREATE INDEX CONCURRENTLY orders_cust_idx ON orders(customer_id);

Property	Detail
Lock	ShareUpdateExclusive (writes proceed)
Cost	Two scans; slower wall clock
Failure	Leaves invalid index; `REINDEX` or drop & retry

\di+
SELECT indexname, indexdef FROM pg_indexes
 WHERE schemaname='public' AND tablename='orders';

DROP INDEX IF EXISTS orders_cust_idx;
DROP INDEX CONCURRENTLY orders_cust_idx;