In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array? In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index?    

SET enable_seqscan to FALSE;
EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata-&gt;&gt;'types') @&gt; '{52, 53}'


Output of EXPLAIN

Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
  Filter: (int_array_cast((metadata -&gt;&gt; 'types'::text)) @&gt; '{10,14}'::integer[])
  Rows Removed by Filter: 37
Planning Time: 0.201 ms
Execution Time: 0.197 ms


Table Structure

CREATE TABLE "items" ( 
    "item_uuid" UUid NOT NULL,
    "metadata" JSONB,
    PRIMARY KEY ("item_uuid") 
);


int_array_cast function defintion

CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[] 
AS 
$$
  SELECT CAST($1 AS INT[])
$$
IMMUTABLE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT


Index created using GIN

CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata-&gt;&gt;'types'))


Sample items in the table

item_uuid | metadata
--------------------------------------------------
        1 | {"types":"{1,2}", "name": "item_name1"}
        2 | {"types":"{10,11}", "name": "item_name2"}
        3 | {"types":"12", "name": "item_name3"}
        3 | {"name": "item_name4"}
If you want an index to be used for searching in an array, you need to first store the values as a proper array, not as a string with comma separated values that are enclosed with curly braces, e.g: {"types": [1,2], "name": "item_name1"}

Then you need to access the value as JSONB, not as text. So you need to use -&gt; not -&gt;&gt;. 

SELECT * 
FROM items
WHERE metadata -&gt; 'types'  @&gt; '[52,53]'::jsonb


And finally you need to index the types array to make that work, not the complete content of the metadata column:

create index idx_metadata_types on items using gin  ((metadata -&gt; 'types'));




To test the index usage, you need a substantially larger table than the one you have with just 38 rows. 

Using this query:

explain (analyze, buffers)
SELECT * 
FROM items
WHERE metadata -&gt; 'types' @&gt; '[52,53]'::jsonb


On a table with 100000 rows I then get the following plan (using Postgres 11 on my Windows 10 laptop):

Bitmap Heap Scan on items  (cost=5.38..103.08 rows=100 width=78) (actual time=0.315..0.422 rows=54 loops=1)
  Recheck Cond: ((metadata -&gt; 'types'::text) @&gt; '[52, 53]'::jsonb)
  Heap Blocks: exact=51
  Buffers: shared hit=57
  -&gt;  Bitmap Index Scan on idx_metadata_types  (cost=0.00..5.35 rows=100 width=0) (actual time=0.298..0.298 rows=54 loops=1)
        Index Cond: ((metadata -&gt; 'types'::text) @&gt; '[52, 53]'::jsonb)
        Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms