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?

  • 3
    A GIN index would be usable: postgresql.org/docs/current/gin-builtin-opclasses.html. But a properly normalized table with a declared foreign key might be a better choice to begin with – a_horse_with_no_name Apr 13 at 19:14
  • How would I declare the gin index? I tried this: create index i2_tag_data on test.id_data2 using GIN((ids::jsonb)) It returns success, but I need it to index each specific value in the list. Regarding the normalized table, I was trying to avoid a table with 1billion+ potential rows – Jay Schimmel Apr 13 at 21:27
  • Yes, that is the way to index a jsonb column (the cast is completely useless as the column is already defined as jsonb). You don't "index specific values" with a database index. You index the values of a column for all rows. – a_horse_with_no_name Apr 14 at 5:28
  • 1
    Your approach is no good. Don't use JSON for that. A large normalized table is much better in every respect. – Laurenz Albe Apr 14 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.