PostgreSQL jsonb列查询优化：索引无效如何提升查询速度？

首先，咱们得先搞清楚为什么你加的GIN索引没起作用——普通GIN索引（包括jsonb_path_ops类型）只对PostgreSQL jsonb的特定操作符生效，比如@>（包含）、?（存在键）这类针对jsonb结构本身的查询，而你用的data->>'name' like 'dummy_%'和data->>'size' >= '500000'是提取字段后的常规字符串/数值查询，GIN索引根本覆盖不到这类场景，所以加不加都没变化。

一、优化索引与查询语句的具体方案 #

1. 创建针对性的表达式索引 #

既然你的查询是针对jsonb里的name和size字段做过滤，那应该为这两个字段单独创建B-tree索引（B-tree是PostgreSQL处理前缀like、范围查询最擅长的索引类型）：

-- 为name字段创建前缀匹配优化的B-tree索引（PostgreSQL默认支持前缀like走B-tree）
CREATE INDEX idx_dummy_jsonb_name ON dummy_jsonb ((data->>'name'));

-- 注意！你的size是数值类型，但查询里用了字符串比较，先转成bigint再建索引
CREATE INDEX idx_dummy_jsonb_size ON dummy_jsonb ((data->>'size')::bigint);

2. 修正查询语句的逻辑错误+性能优化 #

你原来的data->>'size' >= '500000'是字符串字典序比较，这会导致错误结果（比如'99999'会被认为比'500000'大），同时也没法用到数值索引。修正后的查询应该是：

SELECT * 
FROM dummy_jsonb 
WHERE data->>'name' LIKE 'dummy_%' 
  AND (data->>'size')::bigint >= 500000 
ORDER BY (data->>'size')::bigint DESC 
OFFSET 50000 LIMIT 10;

3. 进阶：创建复合索引应对联合查询+排序 #

如果这个查询是高频场景，可以创建复合B-tree索引，把过滤条件和排序字段都包含进去，让数据库直接从索引里取数据，不用再回表排序：

CREATE INDEX idx_dummy_jsonb_name_size_desc ON dummy_jsonb 
((data->>'name'), (data->>'size')::bigint DESC);

这个索引能覆盖name的前缀过滤、size的范围查询，以及最后的排序操作，会大幅提升查询速度。

二、关系表vs jsonb：什么时候该用哪个？ #

如果你的数据结构是固定的（像这里的name、size、create_at都是每个记录都有的固定字段），关系表确实是更优选择——PostgreSQL对关系型数据的优化非常成熟，索引类型更丰富，查询计划更高效，避免了jsonb字段提取的额外开销。

jsonb的优势场景是半结构化数据：比如有些记录有额外的自定义字段，有些没有；或者字段结构经常变化，用关系表需要频繁改表结构的情况。这种时候用jsonb才能体现灵活性的价值。

三、关于MongoDB的性能对比 #

MongoDB在文档型查询场景下确实有一些优化，但不能直接说这类场景就更适合MongoDB：

• 首先，你用的是PostgreSQL 9.6，这是一个比较老的版本（2016年发布），最新的PostgreSQL版本（12+）对jsonb的查询优化做了大量改进，比如支持更高效的表达式索引、并行查询、jsonb路径查询的优化等，升级版本后性能可能会有明显提升。
• 其次，PostgreSQL支持ACID事务、复杂查询（关联、聚合），这些是MongoDB在很多业务场景下不具备的优势。如果你的业务需要事务保障或者复杂分析，PostgreSQL的价值远大于单纯的查询速度。

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