mxKZDqP2

1SET enable_seqscan to FALSE;
2EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}' 
3     
4Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
5  Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
6  Rows Removed by Filter: 37
7Planning Time: 0.201 ms
8Execution Time: 0.197 ms 
9     
10CREATE TABLE "items" ( 
11    "item_uuid" UUid NOT NULL,
12    "metadata" JSONB,
13    PRIMARY KEY ("item_uuid") 
14); 
15     
16CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[] 
17AS 
18$$
19  SELECT CAST($1 AS INT[])
20$$
21IMMUTABLE
22LANGUAGE SQL
23RETURNS NULL ON NULL INPUT 
24     
25CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types')) 
26     
27item_uuid | metadata
28--------------------------------------------------
29        1 | {"types":"{1,2}", "name": "item_name1"}
30        2 | {"types":"{10,11}", "name": "item_name2"}
31        3 | {"types":"12", "name": "item_name3"}
32        3 | {"name": "item_name4"}