Indexing JSONB
A GIN index can support many JSONB containment and key-existence operations. The default jsonb_ops operator class supports a broader set of operators, while jsonb_path_ops supports a narrower set with different storage and performance characteristics. The best choice depends on the operators used by the workload.
An index is only useful when the query expression and operator match its capabilities. For frequent scalar extraction, an expression index may be more appropriate than a broad GIN index. Test representative data and inspect query plans rather than assuming every JSON query will use an index.
GIN index for containment
CREATE INDEX idx_events_payload_gin
ON events USING GIN (payload);
SELECT id
FROM events
WHERE payload @> '{"type":"signup"}'::jsonb;💡 The GIN index may help containment queries. Verify the plan with representative table size and statistics, and choose an operator class based on the operations required.
Key Takeaways
- GIN is commonly used for JSONB containment and key operations.
- Expression indexes can target frequently queried JSON fields.
- Validate index benefits using EXPLAIN ANALYZE.
Knowledge Check
Which index type is commonly used for JSONB containment queries?