PostgreSQL indexing JSONB array [duplicate]

Question

Here is my json

jsondata ------------------------------------ {"key1": 1, "keyset": [10, 20, 30]} {"key1": 1, "keyset": [10, 20]} {"key1": 1, "keyset": [30]} {"key1": 1 } {"key1": 1, "key2": 1}

I tried creating index keyset for above example
1st index using btree

CREATE INDEX test_keyset ON test_table (jsondata->'keyset');

2nd index using gin

CREATE INDEX test_keyset ON test_table USING GIN(jsondata->'keyset');

and query to select keyset value 10,

SELECT jsondata FROM test JOIN LATERAL jsonb_array_elements_text(jsondata->'keyset') a(v) ON TRUE WHERE a.v::integer = 10;

but it is doing sequential scan(checking all rows), can anyone suggest me about which indexing method is correct(btree or gin) and efficient way of getting data from json using indexing for above example, I'm new to postgres

Maybe this can help: stackoverflow.com/a/18405706/3270427

McNets
– McNets

2017-04-15 18:30:47 +00:00
Commented Apr 15, 2017 at 18:30 — McNets
– McNets, Commented Apr 15, 2017 at 18:30

klin · Accepted Answer · 2017-04-15 18:47:04Z

Use gin index on the expression jsondata->'keyset':

create index test_keyset on test using gin((jsondata->'keyset'));

You should use the expression in your queries in conjunction with the @> operator:

select jsondata from test where jsondata->'keyset' @> '10' jsondata ------------------------------------- {"key1": 1, "keyset": [10, 20, 30]} {"key1": 1, "keyset": [10, 20]} (2 rows)

Test if the planner can use the index:

set enable_seqscan to off; explain analyse select jsondata from test where jsondata->'keyset' @> '10' QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=8.00..12.02 rows=1 width=55) (actual time=0.024..0.025 rows=2 loops=1) Recheck Cond: ((jsondata -> 'keyset'::text) @> '10'::jsonb) Heap Blocks: exact=1 -> Bitmap Index Scan on test_keyset (cost=0.00..8.00 rows=1 width=0) (actual time=0.014..0.014 rows=2 loops=1) Index Cond: ((jsondata -> 'keyset'::text) @> '10'::jsonb) Planning time: 0.576 ms Execution time: 0.066 ms (7 rows)

Collectives™ on Stack Overflow

PostgreSQL indexing JSONB array [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related