主页 / dba / 问题 / 25890
Accepted
davetapley
Asked: 2012-10-13 21:21:38 +0800 CST

更新部分索引的字段是在 Postgres 的索引中添加还是删除它

  • 772

我们可以为 Postgres 中的字段创建部分索引。

我正在考虑使用它来阻止archived位于以下索引中的行created_at field：

  1. 首先我们创建索引：

    test=# CREATE INDEX idx_my_table_created_at ON my_table(created_at) WHERE NOT(archived);

  2. 我们插入两行，一行进入，另一行idx_my_table_created_at不进入：

     id |         created_at         | archived
----+----------------------------+----------
  2 | 2012-10-12 17:39:17.28511  | t
  1 | 2012-10-12 17:38:55.531278 | f

  3. 现在我们UPDATE有一行目前在索引中，因此它不应该在索引中：

    test=# UPDATE my_table SET archived = true WHERE id = 1;


我的问题：id = 1还在idx_my_table_created_at吗？

这个问题是从我问过的一个问题的答案开始的，这个问题是关于如何处理一旦过时就不会被查询的行，因此archived。

postgresql performance

1 个回答

  1. Best Answer

    （注意：我不是 Pg 内脏方面的专家。这只是我的理解。您应该真正阅读手册的mvcc和内部部分）。

    是的，最初带有的行id = 1仍在索引中，尽管索引的WHERE子句将其排除在外，因为它已被更新为archived = true。在跟踪的某个点，索引条目将被VACUUM或 autovacuum 清除，从而释放空间。

    原因是PostgreSQL的MVCC设计。其他并发事务可能仍然能够看到旧版本的行id = 1, archived = false-READ COMMITTED具有长时间运行语句的SERIALIZABLE事务，或者在开始后看不到已提交更改的事务。如果在更新行时删除了索引条目，那么这些事务将无法在索引扫描中找到该行，并且会产生错误的结果。

    从技术上讲，实际发生的是旧行仍然存在，它只是有一个xmax集合，所以新的事务在看到它时会忽略它。更新后索引指向该行，与之前相同。在旧行的上方archived = true插入了一个带有的新行，因此任何给定的事务要么看到旧版本，要么看到新版本，而不会同时看到两者。该新行不会添加到索引中。xminxmax

    最终将没有打开的事务仍然可以看到旧行，因此VACUUM将过来删除旧行及其索引条目，释放空间以供重新使用。

    请注意，索引本身没有行可见性信息。您可以 - 而且经常这样做 - 有多个版本的行，每个版本都有索引条目。当事务查找匹配的索引条目时，它会读取所有版本（除非它可以从可见性映射中看出它不需要）并忽略所有版本，除了具有xmin并且xmax使其对该事务可见的版本。

    这是确保 autovaccum 频繁运行的众多充分理由之一。它减少了浪费空间和 I/O 的死索引条目和死行的数量。

    • 4

相关问题