Postgresql – Index not being used on array elements in JSONB column

postgresql

I have a table testuser like this:

CREATE TABLE testuser (id int, data jsonb);

where the following snippet shows the format of the data column:

INSERT INTO testuser (id, data) VALUES (1, '{
  "name": "peter",
  "permissions": [
    {
      "type": "ARTICLE",
      "aggregateReference": "ae8b9fed-f99b-498e-b9ab-b87b1eec94a7"
    }
  ]
}'::jsonb);

Now, I've created a gin index on the permissions field like this:

CREATE INDEX test_user_permission_ix ON testuser USING gin((data -> 'permissions'));

but when I try to generate some additional rows, and run a query, the index is not being used:

SET enable_seqscan=false;
INSERT INTO testuser SELECT * FROM generate_series(1,100);
EXPLAIN ANALYZE SELECT * FROM testuser WHERE data @> '{"permissions" : [{"type":"ARTICLE"}]}';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Seq Scan on testuser  (cost=10000000000.00..10000000170.01 rows=10 width=141) (actual time=26.040..26.453 rows=1 loops=1)
   Filter: (data @> '{"permissions": [{"type": "ARTICLE"}]}'::jsonb)
   Rows Removed by Filter: 10000
 Planning Time: 0.051 ms
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 0.327 ms, Inlining 3.541 ms, Optimization 16.740 ms, Emission 5.626 ms, Total 26.233 ms
 Execution Time: 26.861 ms
(9 rows)

I can see in the pg_indexes table that the type of the permissions field seem to be ::text, but I'm not sure if this affects anything:

 schemaname | tablename |        indexname        | tablespace |                                              indexdef
------------+-----------+-------------------------+------------+-----------------------------------------------------------------------------------------------------
 public     | testuser  | test_user_permission_ix |            | CREATE INDEX test_user_permission_ix ON public.testuser USING gin (((data -> 'permissions'::text)))

Any suggestions on why the index is not being used would be very appreciated!

Best Answer

The left hand side of the WHERE expression must match the expression in the index, in order for the index to be usable (because the index stores a copy of that expression's value).

As your index is created on the expression data -> 'permissions' you need to use that in the query:

select *
from testuser
where data -> 'permissions' @> '[{"type":"ARTICLE"}]';

Note that this doesn't guarantee that the index is used. If the condition returns a substantial part of the table, the Seq Scan is still faster.

For the condition data @> '{"permissions": [{"type": "ARTICLE"}]}' you would need to create an index on data (not an expression)

Related Question