如何为PostgreSQL JSONB字段指定键创建索引以优化查询

通过Sequelize迁移为Entities表添加了JSONB类型的summary列：

queryInterface.addColumn('Entities', 'summary', {
  type: Sequelize.DataTypes.JSONB,
})

使用的PostgreSQL版本：

PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

数据行的summary字段结构通常为：

{ 
  summary: {
    foobar: 123.456
  }
}

需要查询summary.foobar值小于指定参数的记录，对应的Sequelize查询代码：

where: {
  summary: {
    foobar: {
      [Sequelize.Op.lt]: parseFloat(maxFoobar),
    },
  },
}

生成的SQL语句为：

SELECT "id", "name", "type", "geometry", "summary", "createdAt", "updatedAt"
FROM "Entities" AS "Entity"
WHERE CAST(("Entity"."summary"#>>'{foobar}'::text[]) AS DOUBLE PRECISION) < 64
ORDER BY "Entity"."createdAt" DESC
LIMIT 100
OFFSET 0;

在15万行数据的场景下，该查询耗时300-400ms，执行计划显示当前采用并行全表扫描，需要通过创建索引优化性能。

针对该查询的过滤条件，需要创建函数表达式索引，让PostgreSQL可以直接利用索引匹配过滤规则，避免全表扫描。

1. 基础函数索引（满足过滤需求） #

直接执行SQL创建索引：

CREATE INDEX idx_entities_summary_foobar_double ON "Entities"
USING btree ((("summary" #>> '{foobar}')::double precision));

如果通过Sequelize迁移创建，在迁移文件中添加：

queryInterface.addIndex('Entities', {
  fields: [
    Sequelize.literal('((("summary" #>> \'{foobar}\')::double precision)')
  ],
  name: 'idx_entities_summary_foobar_double'
});

这里选择btree索引类型，因为它对<这类范围比较操作的支持最优。

2. 复合索引（同时优化过滤与排序） #

由于查询还包含ORDER BY "createdAt" DESC和LIMIT 100，可以创建复合索引，让数据库直接从索引中获取已排序的结果，避免额外的排序开销：

CREATE INDEX idx_entities_foobar_createdat ON "Entities"
USING btree ((("summary" #>> '{foobar}')::double precision), "createdAt" DESC);

对应的Sequelize迁移代码：

queryInterface.addIndex('Entities', {
  fields: [
    Sequelize.literal('((("summary" #>> \'{foobar}\')::double precision)'),
    'createdAt'
  ],
  order: [['createdAt', 'DESC']],
  name: 'idx_entities_foobar_createdat'
});

该索引可以同时覆盖过滤条件和排序需求，进一步降低查询耗时。

注意事项 #

• 若部分行的summary字段缺失foobar键，转换后会得到NULL，这类记录不会被< 64的条件匹配，无需额外处理；若需要包含NULL值，可调整查询条件。
• 索引会增加写入操作（插入、更新、删除）的开销，若表的写入频率极高，需权衡查询性能提升与写入成本的平衡。

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