Search code examples
postgresqlindexingjsonb

PostgreSQL not using index scan over GIN index on JSONB


I have a table in PostgreSQL containig one JSONB column. The JSON looks like this- {'key':some_key, 'value': some_value}.
I indexed key in JSON using -

CREATE INDEX t3_index ON t3 USING GIN ((data->'key'));

The table's schama looks like-

                Table "public.t3"
 Column | Type  | Collation | Nullable | Default 
--------+-------+-----------+----------+---------
 data   | jsonb |           |          | 
Indexes:
    "t3_index" gin ((data -> 'key'::text))

When I query using key, PostgreSQL is doing a sequnetial scan.

psql_eth=> explain (analyze, buffers) select * from t3 where data->>'key'='ZGJVcGdyYWRlXzIwMTcwNzE0ZGVkdXBsaWNhdGVEYXRh';
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Seq Scan on t3  (cost=0.00..4201.65 rows=377 width=289) (actual time=0.017..42.976 rows=1 loops=1)
   Filter: ((data ->> 'key'::text) = 'ZGJVcGdyYWRlXzIwMTcwNzE0ZGVkdXBsaWNhdGVEYXRh'::text)
   Rows Removed by Filter: 75049
   Buffers: shared hit=3142
 Planning time: 0.068 ms
 Execution time: 42.996 ms
(6 rows)
  1. Why is PostgreSQL doing seq scan?
  2. How can I make the query result faster? How to make it use GIN index?

Solution

  • From the documentation:

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

    The index is not applicable for the equality operator (=). You can use simple btree index instead:

    create index on t3 ((data->>'key'));