Reputation: 1343
I have a jsonb field with data like:
{"state": "initialize_done_state", ... }
GIN index on this field:
CREATE INDEX index_activities_on_data ON activities USING gin (data)
and performing a query:
select count(*)
from activities a
where a.created_at >= (date_trunc('month', current_date)::timestamp AT TIME ZONE 'MSK') and
--a.data ->> 'state' = 'issued_success_state';
a.data @> '{ "state": "issued_success_state" }';
when I use @> operator in WHERE clause, gin index is used:
Aggregate (cost=406.91..406.92 rows=1 width=8)
-> Bitmap Heap Scan on activities a (cost=32.75..406.67 rows=95 width=0)
Recheck Cond: (data @> '{"state": "issued_success_state"}'::jsonb)
Filter: (created_at >= timezone('MSK'::text, (date_trunc('month'::text, (('now'::cstring)::date)::timestamp with time zone))::timestamp without time zone))
-> Bitmap Index Scan on index_activities_on_data (cost=0.00..32.73 rows=364 width=0)
Index Cond: (data @> '{"state": "issued_success_state"}'::jsonb)
and is NOT used, when I use ->> operator:
Aggregate (cost=59093.42..59093.43 rows=1 width=8)
-> Seq Scan on activities a (cost=0.00..59092.23 rows=477 width=0)
Filter: (((data ->> 'state'::text) = 'issued_success_state'::text) AND (created_at >= timezone('MSK'::text, (date_trunc('month'::text, (('now'::cstring)::date)::timestamp with time zone))::timestamp without time zone)))
Please explain why?
Upvotes: 0
Views: 331
Reputation: 3690
Late to the party, but if it still helps, see below.
Like the documentation says -
for type jsonb, jsonb_ops is the default.
Since you have not provided any operator class while creating index, the default is choosen for you.
For jsonb_ops
operator class the only supported Operators are -
? ?& ?| @> @? @@
Upvotes: 0
Reputation: 121919
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 @>.
You can create a B-tree index which can be used with ->>
operator, e.g.
CREATE INDEX ON activities ((data->>'state'));
Upvotes: 2