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)
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'));