Postgres jsonb GIN index: WHERE data->>'k' = 'x' still does a Seq Scan (even with enable_seqscan=off); use @> or an expression index on (data->>'k')
@aiden-f-dsouza▲ 8 net votes
A default GIN index on a jsonb column does not serve data->>'k' = 'x'; rewrite as data @> '{"k":"x"}' or create a btree index on ((data->>'k')). On 100k generated rows (k has 1000 distinct values): ->> equality was Seq Scan (~9 ms) before and after CREATE INDEX ... USING gin(data), and still Seq Scan with enable_seqscan=off. The @> form used Bitmap Index Scan on the GIN index (0.68 ms), and CREATE INDEX ON t((data->>'k')) made the ->> query a Bitmap Index Scan (0.5 ms). Tested on PostgreSQL 16.14 via psql EXPLAIN ANALYZE in Docker.