Index created for PostgreSQL jsonb column not utilized

Question

I have created an index for a field in jsonb column as:

create index on Employee using gin ((properties -> 'hobbies'))

Query generated is:

CREATE INDEX employee_expr_idx ON public.employee USING gin (((properties -> 'hobbies'::text)))

My search query has structure as:

SELECT * FROM Employee e
    WHERE e.properties @> '{"hobbies": ["trekking"]}'
    AND e.department = 'Finance'

Running EXPLAIN command for this query gives:

Seq Scan on employee e  (cost=0.00..4452.94 rows=6 width=1183)
    Filter: ((properties @> '{"hobbies": ["trekking"]}'::jsonb) AND (department = 'Finance'::text))

Going by this, I am not sure if index is getting used for search.

Is this entire setup ok?

user330315 · Accepted Answer · 2020-08-20 11:15:10Z

3

The expression you use in the WHERE clause must match the expression in the index exactly, your index uses the expression: ((properties -> 'hobbies'::text)) but your query only uses e.properties on the left hand side.

To make use of that index, your WHERE clause needs to use the same expression as was used in the index:

SELECT * 
FROM Employee e
WHERE (properties -> 'hobbies') @> '["trekking"]'
  AND e.department = 'Finance'

However: your execution plan shows that the table employee is really tiny (rows=6). With a table as small as that, a Seq Scan is always going to be the fastest way to retrieve data, no matter what kind of indexes you define.

answered Aug 20, 2020 at 11:15

user330315

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

1 Comment

Mandroid Over a year ago

How can I use it with spring data query. So input would be hobby. If I try to write native query as: @Query(value = "SELECT * FROM Employee e " + "WHERE (e.properties -> 'hobbies') @> :hobby AND e.department = 'Finance' ", nativeQuery = true) hobby is framed as say, ["trekking"]. It refuses to identiy @>.

Collectives™ on Stack Overflow

Index created for PostgreSQL jsonb column not utilized

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