使用 postgresql 10.6。我有一个名为place的表，其中包含一个jsonb城市字段，其中包含一个 json 数组。我创建了一个关于城市的杜松子酒指数。城市 json 数组将有数万条记录。我需要在 where 子句中查询这个数组以获取 5000 个城市名称。该查询绝对应该使用 gin 索引来提高性能。就我在执行计划中看到的而言，我在下面的小提琴示例查询没有使用索引。应如何编写此查询以使用索引以使其快速运行。
表定义：
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"}]');
我的查询：
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"}'
一旦取消嵌套 JSON，就不能再使用索引。我认为您正在寻找：
select jsonb_array_elements(p.cities) item
from place p
where p.cities @> '[{"name": "paris"}]'
or p.cities @> '[{"name": "dakar"}]'
或更明确地说：
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"}]'
只有在表中有很多行（不是数组中的元素！）并且 WHERE 条件将许多行减少到一些时，才会使用 GIN 索引。
如果您将所有内容都存储在一个巨大的 JSON 值中，那么没有索引会改善您的查询。您应该规范化您的数据模型 - 这似乎很容易做到，因为您似乎没有任何动态结构并且所有元素共享相同的键（=列）。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句