在PostgreSQL中的JSONB条目中索引一个特定键/值的最佳方法是什么？

Question

我希望使用索引来加快Postgres数据库中的搜索速度。这是我第一次使用索引，所以我不确定什么是最好的方法。

我想使用索引的表如下所示：

| id [id]             | data [jsonb]   |
|---------------------+------------------------|
| a11                 | [                      |
|                     |   {                    |
|                     |     "itemId": "p11",   |
|                     |     "position": 4      |
|                     |   },                   |
|                     |   {                    |
|                     |     "itemId": "p12",   |
|                     |     "position": 2      |
|                     |   }                    |
|                     | ]                      |
| ------------------- | ---------------------- |
| a12                 | [                      |
|                     |   {                    |
|                     |     "itemId": "p13",   |
|                     |     "position": 3      |
|                     |   },                   |
|                     |   {                    |
|                     |     "itemId": "p14",   |
|                     |     "position": 6      |
|                     |   }                    |
|                     | ]                      |
| ------------------- | ---------------------- |
| a13                 | [                      |
|                     |   {                    |
|                     |     "itemId": "p11",   |
|                     |     "position": 3      |
|                     |   },                   |
|                     |   {                    |
|                     |     "itemId": "p14",   |
|                     |     "position": 2      |
|                     |   }                    |
|                     | ]                      |

这个表有数千个条目，我想要做的是快速找到包含特定itemId的所有条目。例如:在本例中，查找"p11“将返回ids为"a11”和"a13“的条目。

做这件事最好的方法是什么？

我的想法是使用GIN索引创建一个索引表，并且只对数据列的 itemIds进行索引。位置和其他数据不需要索引。

这会是杜松子酒索引的正确用法吗？如果没有，我应该用什么代替？SQL语句会是什么样的呢？

谢谢你的帮助。

Answer 1

简单的方法是在整个JSON上创建一个GIN索引：

CREATE INDEX ON tab USING gin (data);

然后查询如下：

SELECT id FROM tab WHERE data @> '[ { "itemId": "p11" } ]';

如果希望保持索引的大小尽可能小，并尽可能快地执行查询，则可以使用以下B树索引：

CREATE INDEX ON tab (id) WHERE data @> '[ { "itemId": "p11" } ]';

该部分索引将更好地支持上述查询，但它可能只对这一查询有用。你必须决定利益是否超过了一般性的损失。