Alexander Ioffe：JSONB路径、GIN还是列？
内容提要
PostgreSQL 17中，对含十个已知可空属性的订单表，规范化列方案在多数工作负载下优于JSONB+GIN。1M行时，范围聚合查询列方案仅5.72ms，GIN需78ms；排序查询从155ms降至亚毫秒。GIN索引不支持范围查询和排序，未类型化路径索引因类型不匹配失效，类型化表达式索引虽改善但仍有堆检查开销。混合方案（已知字段建列+额外JSONB列）性能最佳，建议采用。
延伸解读
类型匹配是表达式索引生效的关键
文章指出，未类型化的路径索引（如 details->>'units_shipped'）存储为文本，而查询中若使用 (details->>'units_shipped')::int 进行数值比较，类型不匹配会导致索引失效，查询退化为全表扫描。只有将索引表达式也显式转换为 int，如 ((details->>'units_shipped')::int)，才能让规划器使用索引。这提醒我们，在创建表达式索引时，必须确保索引表达式与查询谓词的类型完全一致，否则索引形同虚设。
GIN索引的适用边界
GIN索引专为JSONB的包含（@>）查询设计，在等值查找上表现优异，但不支持范围查询（>、<、BETWEEN）和排序（ORDER BY）。当查询涉及这些操作时，规划器会忽略GIN索引，转而进行全表扫描。因此，如果业务中频繁需要按JSONB字段进行范围过滤或排序，单纯依赖GIN索引是不够的，需要结合其他索引策略或考虑调整表结构。
混合模式：性能与灵活性的平衡
文章推荐的混合方案是将已知字段提升为独立列，同时保留一个额外的JSONB列用于存储真正动态的数据。这种设计既能让已知字段的查询利用索引仅扫描（Index Only Scan），获得接近零堆读取的性能，又保留了JSONB的灵活性，避免因频繁变更schema而带来的迁移成本。对于字段已知且查询模式固定的场景，这是优于纯JSONB+GIN的实用选择。
基准测试的局限性
文章强调，测试使用的是合成数据，可能无法完全反映真实数据的分布和相关性。此外，测试仅关注读性能，未涉及写入开销。GIN索引在写入时维护成本较高，而多个表达式索引也会增加写放大。因此，在实际应用中，需要结合自身数据特征和读写比例，谨慎评估不同方案的优劣，不能盲目套用本文结论。
Q&A
在PostgreSQL 17中，对于包含十个已知可空属性的订单表，使用规范化列方案相比JSONB+GIN在性能上有什么优势？
规范化列方案在大多数工作负载下优于JSONB+GIN。例如，在1M行数据上，范围聚合查询（count(*) WHERE units_shipped > 950）列方案仅需5.72ms，而GIN需要78ms；排序查询从155ms降至亚毫秒。列方案还能利用Index Only Scan，避免访问堆，而JSONB+GIN无法做到。
为什么GIN索引不支持范围查询和排序？
GIN索引的jsonb_path_ops操作符类只支持包含操作（@>），不支持范围比较（>、<、BETWEEN）和排序（ORDER BY）。因此，当查询涉及范围条件或排序时，PostgreSQL无法使用GIN索引，只能退化为全表扫描。
在JSONB字段上创建未类型化的路径索引（如details->>'units_shipped'）为什么在数值范围查询中无效？
未类型化的路径索引将值存储为文本类型，而查询中通常需要将值转换为整数（如::int）进行比较。由于类型不匹配，规划器无法使用该索引，导致查询退化为全表扫描。例如，索引是文本类型，而查询条件是整数类型，两者不匹配。
类型化表达式索引（如((details->>'units_shipped')::int)）相比未类型化索引有什么改进？它仍然存在什么开销？
类型化表达式索引将索引表达式转换为与查询匹配的类型，因此规划器可以使用它。例如，范围聚合查询从78ms降至35.2ms，排序查询从155ms降至0.10ms。然而，它仍然需要访问堆来重新检查JSONB提取的值，因为索引表达式不是行的一部分，导致额外的堆块读取（如1M行时26,353个堆块）。
混合方案（已知字段建列+额外JSONB列）为什么性能最佳？
混合方案将已知字段提升为列，并保留一个额外的JSONB列用于未知或动态数据。这样，已知字段的查询可以使用Index Only Scan，避免访问堆，性能最佳。例如，范围聚合查询仅需5.72ms，排序查询亚毫秒。同时，额外的JSONB列保留了灵活性，但增加了少量行宽开销。
如果已经使用JSONB+GIN且无法迁移，应该采取什么优化措施？
如果无法迁移，建议为经常过滤或排序的字段创建类型化btree表达式索引，并确保索引中的类型转换与查询中的类型转换一致。避免使用未类型化的文本索引处理数值字段。使用EXPLAIN ANALYZE验证规划器是否使用了索引，如果出现Parallel Seq Scan，说明谓词和索引类型不匹配。
文章中的基准测试是如何进行的？
作者使用ExoBench（一个MCP服务器）生成合成数据并运行EXPLAIN ANALYZE。ExoBench在临时PostgreSQL实例上生成数据，支持多个规模点（10K、100K、1M行），并返回真实的执行计划和计时。作者通过AI助手描述问题，AI调用ExoBench进行测试，作者观察结果。
