JSONB列字段创建索引：WHERE子句作用及数值类型索引疑问

1. WHERE IS NOT NULL 子句的作用 #

针对你给出的索引创建语句，这个条件的核心作用有三点：

• 过滤无效条目：只将params字段中存在isInternal键且对应值不为null的记录加入索引，排除键不存在或值为null的情况。
• 缩小索引体积：减少不必要的索引条目占用的存储空间，提升索引的维护效率（比如插入、更新时的索引开销）。
• 优化查询匹配：当你的业务查询中包含(params ->> 'isInternal') IS NOT NULL这类条件时，这个部分索引能直接命中，避免扫描全量索引，加快查询速度。

2. 针对priority字段创建索引的注意事项 #

索引名称要求 #

必须使用新的索引名称，PostgreSQL不允许同一模式下存在同名索引。你给出的orders_params_priority_idx是合理的，能清晰区分这是针对params中priority字段的索引。

类型转换选择 #

PostgreSQL中不存在::number这种类型，需要根据priority的实际取值类型选择：

• 如果priority是整数（如1、100、-5这类）：推荐使用::int4（即integer类型，4字节，适合绝大多数整数场景），如果数值范围超大则用::int8（bigint类型）。
• 如果priority是带小数的数值（如3.14、2.5这类）：可以用::numeric（精确数值类型，适合金额、精度要求高的场景）或::float8（double precision，浮点类型，适合精度要求不高的场景）。

修正后的示例语句 #

以整数类型为例，正确的索引创建语句如下：

CREATE INDEX orders_params_priority_idx ON orders (((params ->> 'priority')::int4))
WHERE (params ->> 'priority') IS NOT NULL;

内容的提问来源于stack exchange，提问作者TwittorDrive