CODEWITHSUNDEEP
postgresqljsonb
killjoy
killjoy

Reputation: 1042

postgresql jsonb array query using gin index

Using postgresql 10.6. I have a table called place with a jsonb field of cities which holds a json array. I created a gin index on cities. cities json array will have tens of thousands of records. I need to query this array for 5000 city name in the where clause. The query is absolutely supposed to use gin index for performance. My sample query in below fiddle is not using the index as far as I saw in the execution plan. How this query should be written to use the index so it runs quickly.

Table definition:

CREATE TABLE place (       
    cities jsonb NULL   
);

CREATE INDEX "IX_place_cities" ON place USING gin (cities);

INSERT INTO place
(cities)
VALUES('[{"name": "paris", "continent": "europe"},
{"name": "london", "continent": "europe"},
{"name": "berlin", "continent": "europe"},
{"name": "istanbul", "continent": "europe"},
{"name": "prag", "continent": "europe"},
{"name": "rome", "continent": "europe"},
{"name": "wien", "continent": "europe"},
{"name": "tokyo", "continent": "asia"},
{"name": "beijing", "continent": "asia"},
{"name": "dakar", "continent": "africa"},
{"name": "daresselam", "continent": "africa"},
{"name": "kuala lumpur", "continent": "asia"},
{"name": "barcelona", "continent": "europe"}]');

My query:

select elems.arrayitem
FROM (
  select jsonb_array_elements(place.cities) as arrayitem 
  from place
) as elems
where elems.arrayitem @> '{"name": "paris"}' 
   or elems.arrayitem @> '{"name": "dakar"}'

SQLFiddle

Upvotes: 1

Views: 1802

Answers (1)

user330315
user330315

Reputation:

As soon as you unnest the JSON, you can't use an index anymore. I think you are looking for:

select jsonb_array_elements(p.cities) item
from place p
where p.cities @> '[{"name": "paris"}]'
   or p.cities @> '[{"name": "dakar"}]'

Or more explicitly:

select e.item
from place p
   cross join jsonb_array_elements(p.cities) as e(item)
where p.cities @> '[{"name": "paris"}]'
   or p.cities @> '[{"name": "dakar"}]'

The GIN index will only be used if you a lot of rows in the table (not elements in the array!) and your WHERE condition reduces the many rows to just some.

If you store everything in one giant JSON value in a single row, no index will improve your query. You should normalize your data model instead - which seems quite easy to do as you don't seem to have any dynamic structure and all elements share the same keys (=column).

Upvotes: 5

Related Questions