I have an attribute (ids) defined as jsonb in Postgres that contains a list of values:
CREATE TABLE test.id_data2 ( key bigint NOT NULL, ids jsonb, CONSTRAINT tag_data2_pk PRIMARY KEY (key) );
- ids contains a list of ids that are numeric strings: '["2", "4", "6", "8", "10"]
- some records may have 50+ ids and I may have 20mm records
- this is being used as a cross-reference to find which keys contain a specific value Once the data is stored, I need to find all records that have a specific id:
from test.id_data2 where ids::jsonb ? '8'
This works but results in a full table scan. Is there a way to create an index on the ids column such that it won't do a full table scan?