t=# create table d(i bigserial, j jsonb); CREATE TABLE t=# insert into d(j) select ('{"foreign_data":{ "some_key": '||g||', "src_data": { "VEHICLE": { "title": "615", "is_working": true, "upc": "85121212121", "dealer_name": "CryptoDealer", "id": '||g||' } } }}')::jsonb from generate_series(1,1222600) g; INSERT 0 1222600 t=# create index ji on d (cast (j->'foreign_data'->'src_data'->'VEHICLE'->>'id' as int)); CREATE INDEX
in order to use such fn() based index youhave to "repeat" function in query:
t=# explain analyze select * from d where cast (j->'foreign_data'->'src_data'->'VEHICLE'->>'id' as int) = 1222551; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using ji on d (cost=0.43..8.45 rows=1 width=215) (actual time=0.021..0.021 rows=1 loops=1) Index Cond: ((((((j -> 'foreign_data'::text) -> 'src_data'::text) -> 'VEHICLE'::text) ->> 'id'::text))::integer = 1222551) Planning time: 1.585 ms Execution time: 0.045 ms (4 rows)
as you see cost is tiny and execution is cheap over index. but if you "skip" formalities and run:
t=# explain analyze select * from d where j->'foreign_data'->'src_data'->'VEHICLE'->>'id' = '1222551'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..50122.31 rows=6113 width=215) (actual time=335.996..336.000 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on d (cost=0.00..48511.01 rows=2547 width=215) (actual time=223.548..332.213 rows=0 loops=3) Filter: (((((j -> 'foreign_data'::text) -> 'src_data'::text) -> 'VEHICLE'::text) ->> 'id'::text) = '1222551'::text) Rows Removed by Filter: 407533 Planning time: 0.096 ms Execution time: 343.090 ms (8 rows)
index will not be used