gotchasqlpostgresqlMajor
JSONB GIN index does not accelerate all operators equally
Viewed 0 times
jsonb gin indexjsonb expression indexjsonb operator performancecontainment operatorarrow operator index
Problem
A GIN index on a JSONB column speeds up @>, ?, ?|, ?& operators but NOT ->> or -> with equality. Queries like WHERE data->>'email' = 'x' perform a full sequential scan even with a GIN index present.
Solution
Use a GIN index for containment and key-existence queries. For equality on a specific key, create an expression index:
-- For containment queries (fast with GIN):
CREATE INDEX idx_data_gin ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"status": "active"}';
-- For equality on a specific key (needs expression index):
CREATE INDEX idx_data_email ON events ((data->>'email'));
SELECT * FROM events WHERE data->>'email' = 'user@example.com';
-- For containment queries (fast with GIN):
CREATE INDEX idx_data_gin ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"status": "active"}';
-- For equality on a specific key (needs expression index):
CREATE INDEX idx_data_email ON events ((data->>'email'));
SELECT * FROM events WHERE data->>'email' = 'user@example.com';
Why
GIN indexes for JSONB store an inverted index of keys and values suitable for containment and existence checks. They cannot satisfy an equality predicate on an extracted text value because that extraction produces a scalar, not a JSONB structure.
Gotchas
- jsonb_path_ops GIN opclass is smaller and faster for @> but drops support for ? operators
- Expression indexes on JSONB extractions are B-tree, not GIN, and only cover one key
- EXPLAIN ANALYZE is the only reliable way to confirm index usage
Code Snippets
EXPLAIN to verify which JSONB query patterns use the GIN index
-- Check which index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE data @> '{"type": "click"}';
-- vs
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE data->>'type' = 'click';Context
When adding indexes to JSONB columns expecting all query patterns to benefit
Revisions (0)
No revisions yet.