PostgreSQL JSONB GIN Index multi condition

Question

I'm using PostgreSQL 13 and I have a table that has a JSONB column and GIN index.

My question is to find a better way for the following query. I have multiple IDs and sometimes I need to find them.

My Schema:

id  timestamp   data    
404599  2022-01-01 00:15:02.566 {"env": "worker", "id":1, "name": "foo", "lang": "en"}
404600  2022-01-01 00:15:02.685 {"env": "worker", "id":2, "name": "foo", "lang": "fr"}
404601  2022-01-01 00:15:02.808 {"env": "worker", "id":3, "name": "foo", "lang": "ru"}
404602  2022-01-01 00:15:03.023 {"env": "worker", "id":3, "name": "foo", "lang": "de"}
404603  2022-01-01 00:15:03.170 {"env": "worker", "id":4, "name": "foo", "lang": "tr"}

My Query:

select * from foo where data @>  '{"id": 1}' or data @> '{"id": 2}' or data @> '{"id": 3}';

I want to use the GIN index for the "data" column but I couldn't find any better way that looks like the "in" operator.

The example is nice, but please explain how to extrapolate it to reality. A 5 row table probably doesn't need an index, and a query returning 60% of a table probably doesn't benefit from an index either. — jjanes
– jjanes, Commented Feb 16, 2022 at 14:43
The above table is not real. It's just an example. :) My real table size is almost 2 TB and it has greater than 100 million rows. — enesgur
– enesgur, Commented Feb 18, 2022 at 8:14

Laurenz Albe · Accepted Answer · 2022-02-16 11:33:23Z

1

That is the best way to query with a GIN index on the JSON column.

If all your queries look like that, using a b-tree index would be more efficient:

CREATE INDEX ON foo ((data ->> 'id'));

SELECT * FROM foo WHERE data ->> 'id' IN (1, 2, 3);

answered Feb 16, 2022 at 11:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

enesgur Over a year ago

Thank you for your reply. It seems that I have to use that bad syntax.

Collectives™ on Stack Overflow

PostgreSQL JSONB GIN Index multi condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related