主页 / dba / 问题 / 323949
Accepted
Zach Smith
Asked: 2023-02-23 15:44:18 +0800 CST

为什么在 PostgreSQL 索引中搜索最近删除的值需要这么长时间？

  • 772

为了测试删除查询是否按预期工作，我想运行一个简单的选择查询，但查询只是挂起并且永远不会完成。看起来pg_stat_activity查询正在运行，并且在和 其他状态wait_event之间发生变化（包括，令人惊讶的是，）。DataFileReadNULLWalWrite

解释的查询：

explain select *
from public.some_table
where foreign_id = 99
limit 1

显示预期的查询计划：

Limit  (cost=0.58..4.51 rows=1 width=45)
  ->  Index Scan using idx_unique_cols on "some_table"  (cost=0.58..32.04 rows=8 width=45)
        Index Cond: (foreign_id = 99)

idx_unique_cols有这个 DDL：

CREATE UNIQUE INDEX idx_unique_cols ON public."some_table" USING btree (foreign_id DESC, col1, col2 DESC, foreign_id_2);

该表相当大 - 大约 380GB，我预计每个foreign_id. 我期待一个相当快速的索引扫描来确定没有 a 为foreign_id99 的行。但这显然没有发生，我不知道为什么查询会花费这么长时间。

问题：

  1. 为什么这个查询需要等待一个WalWrite事件（我假设这意味着选择查询，出于某种原因，需要写入预写日志）
  2. 查看索引，为什么要花这么长时间才能确定索引中的值不存在？
  3. 对我知道的值运行相同的查询foreign_key比some_table立即返回中存在的值高得多。为什么对低于表中现有值的值运行查询需要这么长时间才能返回？
postgresql

1 个回答

  1. Best Answer

    我想到的唯一场景是foreign_id = 99最近有很多行被删除了。在 PostgreSQL 中，DELETE不会立即从表（和索引！）中删除行，而是将它们标记为不可见。稍后，autovacuum 将实际删除已删除的条目。如果 autovacuum 尚未处理该表，则会发生以下情况：

    • 所有删除的条目仍在索引中，PostgreSQL 必须获取表行以确定它们已被删除，这可能需要很长时间

    • 如果表行真的死了（没人能再看到它们），PostgreSQL 将“杀死索引条目”（释放表中的空间并将表和索引中的条目标记为死），以便后续查询可以自救努力

    这篇文章中有更多信息。

    如果我的理论是正确的，那么第二次运行相同的查询应该会快得多，而且VACUUM速度会更快。

    • 2

相关问题