CODEWITHSUNDEEP
postgresqljsonb
kimi1990mp
kimi1990mp

Reputation: 141

Gin Index not being used when searching in JSONB column (PostgreSQL) with an array of search terms

I have a JSONB column called myContent and one of the objects inside the JSON of each row looks like this:

  "metadata": {
    "key": "key",
    "teamBased": false,
  }

I want to select all rows that have teamBased as false. This is how my query looks like:

set enable_seqscan=false;
EXPLAIN ANALYZE
SELECT *
FROM table n
WHERE n.key = 'key'
AND myContent @> ALL(array[:searchTerms]::jsonb[]));

I send searchTerms as an array from my Java application and in this case that array looks like this:

 ['{"metadata":{"teamBased":false}}']

I've created a GIN index that looks like this:

  create index "myIndex"
    on myTable using gin (myContent);

When I look at the output of explain analyze query I see that it's not using my index. However, if I change my query to this


set enable_seqscan=false;
EXPLAIN ANALYZE
SELECT *
FROM table n
WHERE n.key = 'key'
AND myContent @> '{"metadata":{"teamBased":false}}';

it does use it, so I'm assuming it has a problem with the array of search terms that I have in my original query. However, I really need to have the query like that because this query is used in other cases too, when I need more than one search term.

What could be the problem with this? Also, how can I modify my GIN index to not be set to the entire JSON, but rather in one of the objects, for example metadata?

Thank you!

Upvotes: 0

Views: 1915

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

GIN indexes don't support @> ALL, only @>.

The documentation says:

The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.

Upvotes: 1

Related Questions