我有一个 PostgreSQL 表,其名称item_log
为包含各种属性log
的类型列jsonb
。这些属性之一是delete_log
. 我根据字段和属性中值created_at
的存在来查询该表。non-null
delete_log
SELECT item_id
FROM item_log
WHERE created_at >= CURRENT_DATE - INTERVAL '15 days'
AND (log->>'delete_log') IS NOT NULL;
我正在考虑使用不同的索引策略来优化查询:
这是我尝试过的,
创建索引created_at
:当我没有将条件放入
(log->>'delete_log') IS NOT NULL
预期的查询中时,查询变得更快。当我提出这个条件甚至选择这个字段时,大约花了 40 分钟。
我猜想查询速度减慢是由于包含delete_log
. 当查询该字段时,它开始读TOAST
表并执行deTOAST
操作,这会显着增加 I/O 时间。
现在为了优化查询,我有几个选项:
- 添加复合索引
(created_at, log->>delete_log)
- 添加索引,
(log->>delete_log)
因为我已经有索引了created_at
?
另外,我还有一个困惑。
- 我应该选择什么索引类型
log->>delete_log
? log
并且,就上述查询的性能或存储而言,它与对整个字段本身进行索引有何不同?
使用 GIN 索引对整个 JSON 进行索引对您的查询没有多大帮助。
最好的索引是部分索引,它仅对具有非 NULL 的行进行索引
delete_log
:该索引很小,并且不必对不满足条件的行进行修改
WHERE
。缺点是该索引可能只对单个查询有用,并且您希望在接收很多 s 的表上使用尽可能少的索引INSERT
。因此,如果您对该表有其他查询,总体上更好的解决方案可能是使用不太具体的索引,该索引不是当前查询的完美选择,但也可以支持您的其他查询。jsonb上的GIN索引很大,无法用于查询
idxcol->> 'a' IS NOT NULL
。您可能出于其他原因需要它,但它不会对此查询执行任何操作。上的复合 btree 索引
(created_at, (log->>delete_log))
是一个很好的索引。它将避免 deTOAST 数据的需要(前提是选择列表正是您显示的内容)。它在索引内不会特别有效,因为两列都没有测试简单的相等性,因此它需要扫描满足不等式条件的索引的整个部分,单独删除未通过 IS NOT NULL 的行。但如果您只需要避免 deTOAST,那就足够了。(created_at)
和上的单独索引((log->>delete_log))
可以工作,通过与 BitmapAnd 操作结合使用。但规划者很可能会忽视这样做。问题是规划器没有考虑 deTOAST 成本,因此如果包含额外索引的唯一目的是避免 deTOAST,它可能不会为此烦恼。同样,如果您有两个索引(created_at, (log->>delete_log))
,并且(created_at)
可能看不到使用两个索引中较大者的价值,因此最好避免使用单列索引,因为它是一个有吸引力的麻烦。