CREATE INDEX events_payload_gin ON events USING gin (payload);EXPLAIN ANALYZE SELECT * FROM events WHERE payload @> '{"user_id":42}';
2. Using jsonb_ops Operator Class
Operator Class
Supports
Size
jsonb_ops (default)
@> ? ?| ?&
Larger
jsonb_path_ops
@> only
Smaller, faster
3. Using jsonb_path_ops Operator Class
CREATE INDEX events_payload_pops ON events USING gin (payload jsonb_path_ops);
4. Indexing Specific JSON Paths
CREATE INDEX events_user_id ON events (((payload->>'user_id')::int));EXPLAIN SELECT * FROM events WHERE (payload->>'user_id')::int = 42;
5. Using Partial Indexes on JSONB
CREATE INDEX events_paid ON events (created_at) WHERE payload @> '{"status":"paid"}';
6. Querying with Indexed JSON
Predicate
Uses Index
payload @> '{...}'
jsonb_ops / jsonb_path_ops
payload ? 'key'
jsonb_ops only
(payload->>'k')::int = N
Expression btree index
7. Understanding GIN Index Performance
Aspect
Detail
Write cost
Higher (multi-key updates); use fastupdate
Disk size
Substantial; prefer path_ops if only @>
VACUUM
Critical to reclaim pending lists
8. Using JSONB Indexes for Full-Text Search
CREATE INDEX events_fts ON events USING gin ( to_tsvector('english', payload->>'description'));SELECT * FROM events WHERE to_tsvector('english', payload->>'description') @@ to_tsquery('postgres & perf');