I have a JSONB field called data with a few keys and there is a field like:
{school_id: [nil, 123456]}
I'm filtering all records where second element is not null using query:
"data -> 'school_id' ->> 1 IS NOT NULL"
How can I apply an index on that field ? I've tried:
"CREATE INDEX data_school_id ON events ((data -> 'school_id'));"
but when I use EXPLAIN I do not see that this index is in use.
=> EXPLAIN for: SELECT "events".* FROM "events" WHERE (data -> 'school_id' ->> 1 IS NOT NULL)
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on events (cost=0.00..211043.04 rows=2096733 width=555)
Filter: (((data -> 'school_id'::text) ->> 1) IS NOT NULL)
(2 rows)
random_page_cost
,cpu_index_tuple_cost
andeffective_cache_size
.EXPLAIN (ANALYZE, BUFFERS)
, not justEXPLAIN
. Consider instructions in the info to the [postgresql-performance] tag.