时间戳

首页 > 解决方案 > 在 postgresql 中索引 josnb 列

postgresql - 在 postgresql 中索引 josnb 列

问题描述

我在 postgresql 表中有一个类型为 jsonb 的列。

{
    .....
    "type": "car",
     "vehicleIds": [
        "980e3761-935a-4e52-be77-9f9461dec4d1","980e3761-935a-4e52-be77-9f9461dec4d2"
    ]
    .....
}

应用程序对这些字段运行查询以获取记录。我只需要为这些字段索引此列。

如何才能做到这一点？

这是以属性作为列名的查询结构：

SELECT * 
FROM Vehicle f 
WHERE f.properties::text @@ CONCAT('$.vehicleIds[*] >', :vehicleId )= true 
  AND f.properties::text @@ CONCAT('$.type >', :type ) = true

标签： postgresqlindexing

解决方案

您使用的查询非常令人困惑，因为它归结为文本搜索查询，因为@@它应用于一个text值。

我也不明白这个'$.type > ...条件。对于像我这样的值，car我期望一个相等运算符，而不是“大于”。与 UUID 一起使用>似乎也没有意义。

如果要搜索类型值car 并包含 ID 列表，则使用“包含”运算符@>是一种更好的方法：

SELECT * 
FROM Vehicle f 
WHERE f.properties @> '{"type": "car", "vehicleIds": ["980e3761-935a-4e52-be77-9f9461dec4d1"]}'

以上可以利用properties列上的 GIN 索引：

create index on vehicles using gin (properties);

如果type始终以相等方式查询键（我假设），则组合索引可能更有效：

create index on vehicles using gin ( (properties ->> 'type'), (properties -> 'vehicleIds') );

您需要安装btree_gin扩展才能创建该索引。

该索引会小一点，但需要不同的查询：

SELECT * 
FROM Vehicle f 
WHERE f.properties ->> 'type' = 'car' 
  AND f.properties -> 'vehicleIds' @> '["980e3761-935a-4e52-be77-9f9461dec4d1"]'

您需要通过查看执行计划来验证是否使用了索引以及哪些索引更有效

推荐阅读