Postgresql 包含JSONB属性的过滤器的索引策略

我通过sequelize迁移以这种方式创建了一个jsonb专栏:

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

我的Postgres版本:

基于aarch64-未知-linux-gnu的PostgreSQL15.2(Debian15.2-1.pgdg110+1)，由GCC(Debian10.2.1-6)编译10.2.1 20210110，64位

我的行通常具有这样的 struct :

{ 
  summary: {
    foobar: 123.456
  }
}

我需要一个将参数与该JSONB摘要的属性进行比较的查询:

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

Sequelize生成的查询:

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

对于150,000行，这需要300-400毫秒:

                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=29685.31..29696.98 rows=100 width=1475) (actual time=81.465..85.319 rows=100 loops=1)
   Output: id, name, type, geometry, summary, "createdAt", "updatedAt"
   Buffers: shared hit=91259 read=17301
   ->  Gather Merge  (cost=29685.31..34772.56 rows=43602 width=1475) (actual time=81.461..85.310 rows=100 loops=1)
         Output: id, name, type, geometry, summary, "createdAt", "updatedAt"
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=91259 read=17301
         ->  Sort  (cost=28685.29..28739.79 rows=21801 width=1475) (actual time=76.879..76.889 rows=50 loops=3)
               Output: id, name, type, geometry, summary, "createdAt", "updatedAt"
               Sort Key: "Entity"."createdAt" DESC
               Sort Method: top-N heapsort  Memory: 309kB
               Buffers: shared hit=91259 read=17301
               Worker 0:  actual time=75.047..75.062 rows=65 loops=1
                 Sort Method: top-N heapsort  Memory: 353kB
                 Buffers: shared hit=29140 read=5589
               Worker 1:  actual time=75.044..75.055 rows=63 loops=1
                 Sort Method: top-N heapsort  Memory: 351kB
                 Buffers: shared hit=29074 read=5532
               ->  Parallel Seq Scan on public."Entities" "Entity"  (cost=0.00..27852.07 rows=21801 width=1475) (actual time=0.184..68.223 rows=20705 loops=3)
                     Output: id, name, type, geometry, summary, "createdAt", "updatedAt"
                     Filter: ((("Entity".summary #>> '{foobar}'::text[]))::double precision < '64'::double precision)
                     Rows Removed by Filter: 31618
                     Buffers: shared hit=91145 read=17301
                     Worker 0:  actual time=0.240..66.849 rows=20034 loops=1
                       Buffers: shared hit=29083 read=5589
                     Worker 1:  actual time=0.081..66.737 rows=19791 loops=1
                       Buffers: shared hit=29017 read=5532
 Planning:
   Buffers: shared hit=40
 Planning Time: 0.815 ms

如何在JSONB键上构建索引以运行查询？