postgresql - 结合其他列对 JSONB 键建立索引

Question

用于搜索 jsonb 中的特定键列，我想在该列上创建索引。

使用:Postgres 10.2

忽略一些不相关的列，我有表 animals 包含这些列(省略一些不相关的列):

animalid PK number
location (text)
type (text)
name (text)
data (jsonb) for eg: {"age": 2, "tagid": 11 }

我需要根据:location 进行搜索, type和tagId 。喜欢:

where location = ? and type = 'cat' and (data ->> 'tagid') = ?

其他要点:

• 只有猫类型的动物才会有标签 ID，这是现在添加的新动物类型。
• 与其他类型的动物相比，整个表中“猫”的数量会较少。
• 该表很大，有数百万行 - 并且已分区。

如何确保搜索速度快？我考虑过的选项:

1. 制作一个单独的表来存储 cats:animal_id , location , tagId (尽管无法FK到分区父表)
2. 在location上创建索引, type和 jsonb key 。
3. 创建一个新的(索引)列 tagId - 对于除猫之外的所有动物来说，这都是 null。

我确实在表上的其他列上有一个索引 - 但对如何创建索引以基于 tagid 搜索猫有点困惑快速地。有什么建议吗？

更新(忽略分区):

(在分区表上测试)

所以我决定采用 Erwin 建议的选项并尝试创建索引

CREATE INDEX ON animals_211 (location, ((data->>'tagid')::uuid)) WHERE  type = 'cat';

并尝试对查询进行解释(使用分区表以保持简单):

explain select * from animals_211 a
where a.location  = 32341
and a.type  = 'cat'
and (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'

从结果来看，它似乎没有使用创建的索引并进行顺序扫描:

Seq Scan on animals_211  e  (cost=0.00..121.70 rows=1 width=327)                                                                                                        |
  Filter: ((location = 32341) AND ((type)::text = 'cat'::text) AND (((data ->> 'tagid'::text))::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'::uuid

更新2(不使用部分索引)

它似乎是部分索引，如果没有它 - 它似乎可以工作:

CREATE INDEX tag_id_index ON animals_211 (location, type, ((data->>'tagid')::uuid))

当我制定解释计划时:

Index Scan using tag_id_index on animals_211 e  (cost=0.28..8.30 rows=1 width=327)                                                                                         
  Index Cond: ((location = 32341) AND ((type)::text = 'cat'::text) AND (((data ->> 'tagid'::text))::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c'::uuid))

Answer 1

基础知识(忽略分区)

根据您的三个“要点”，我建议 partial index在表达式上:

CREATE INDEX ON animals ((data->>'tagid'))
WHERE  type = 'cat';

使用CREATE INDEX CONCURRENTLY ...以避免对同一个表的并发写入访问出现锁定问题。

Postgres 还收集部分索引的特定统计信息，这有助于查询规划器获得适当的估计。 请注意，如果您在创建后立即在 autovacuum 之前测试索引，则需要手动运行 ANALYZE(或 VACUUM ANALYZE) > 可以启动。请参阅:

• PostgreSQL partial index unused when created on a table with existing data
• Index that is not used, yet influences query

如果tagid确实是text之外的其他数据类型，您还可以转换表达式以进行更多优化。请参阅:

• PostgreSQL Index on JSON

您的更新建议tagid存储UUID值。阅读:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
• What is the optimal data type for an MD5 field?

所以考虑这个索引:

CREATE INDEX ON animals (((data->>'tagid')::uuid))  -- !
WHERE  type = 'cat';

需要在 (data->>'tagid')::uuid 周围添加一组额外的括号，以使语法明确。
以及匹配的查询:

SELECT *
FROM   animals
WHERE  location = 32341
AND    type = 'cats'
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';  -- !

或者 - 根据每个谓词的选择性以及可能的查询变体 - 包含 location 以使其成为多列索引:

CREATE INDEX ON animals (location, ((data->>'tagid')::uuid))
WHERE  type = 'cat';

如果您有未按位置过滤的查询，请先使用tagid。请参阅:

• Is a composite index also good for queries on the first field?

由于只有相对较少的行属于“cat”类型，因此索引将相对较小，不包括大部分“数百万行”。我们一开始只需要猫的 tagid 上的索引。双赢。

如果可能，将 json 键 data->>'tagid' 分解为专用列。 (就像您考虑的选项3.)在不适用的情况下可以为空，空存储非常便宜。使存储和索引更便宜，并且查询更简单。

• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

分区

Postgres 10 不支持分区表的父表上的索引。这是在 Postgres 11 中添加的。此后声明性分区得到了很大改进。很多。考虑升级到当前版本 13 或更高版本。

还有“旧式”选项partitioning with inheritance 。然后，您可以为猫建立一个单独的分区，并仅在其中添加一个附加列tagid。 The manual :

For declarative partitioning, partitions must have exactly the same set of columns as the partitioned table, whereas with table inheritance, child tables may have extra columns not present in the parent.

听起来非常合适。但是继承已经不再受到 Postgres 的青睐，所以在这样做之前我会三思而后行。

无论哪种方式 - 无论是声明式还是继承 - 如果您将所有“猫”放在单独的分区中，则非部分索引显然可以完成这项工作:

CREATE INDEX ON cats (location, ((data->>'tagid')::uuid));

查询可以针对分区cats而不是父表:

SELECT *
FROM   cats
WHERE  location = 32341
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';

定位父表也应该有效。 (不确定 Postgres 10。)

SELECT *
FROM   animals
WHERE  type = 'cat'
AND    location = 32341
AND    (data->>'tagid')::uuid = '5e54c1d9-3ea0-4bca-81d6-1000d90cc42c';

但是激活partition pruning为了那个原因。手册:

Note that partition pruning is driven only by the constraints definedimplicitly by the partition keys, not by the presence of indexes.Therefore it isn't necessary to define indexes on the key columns.

应修剪所有其他分区，然后您应该仅对 cats 分区进行索引扫描...