In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

Question

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->>'types') @> '{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 ->> 'types'::text)) @> '{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->>'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"}

• user1822: Note that with just 4 rows, the database will hardly ever use an index to begin with.
• user1822: Why aren't you storing your array as a proper array in JSON? E.g.: "types": [1,2] instead of a string of comma separated elements that contains curly braces?
• jjanes: Your exact example works for me in 9.6. I get a bitmap index scan. Except I had to change item_uuid to type integer, because the data you show in the table is not value for type uuid.
• jjanes: @a_horse_with_no_name, I assume that is why "SET enable_seqscan to FALSE;" was specified. We certainly don't want him to paste 100,000 rows into his question, so tricks like this are necessary and appropriate.
• user1822: @jjanes: but explain analyze should be run on a sufficiently large table. The one used to produce that plan only contains 38 rows

Answer 1

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 -> not ->>.

SELECT * 
FROM items
WHERE metadata -> 'types' @> '[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 -> '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 -> 'types' @> '[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 -> 'types'::text) @> '[52, 53]'::jsonb)
 Heap Blocks: exact=51
 Buffers: shared hit=57
 -> 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 -> 'types'::text) @> '[52, 53]'::jsonb)
 Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms