即使更新的列不是索引列，PostgreSQL是否会更新索引？

Question

我想要使用 GIN 或 GiST 对一个数组列进行索引。然而，GIN 在插入/更新操作中速度较慢，这让我想知道它是否会对性能产生影响 - 即使被索引的列本身保持静态不变。 因此，假设我有一张包含列（A，B，C）的表格，并且B已经建立了索引，如果我只更新了列C，那么索引会被更新吗？

- Salih Kavaf

Answer 1

这要看情况: ^)

通常情况下，即使索引列中没有任何更改，PostgreSQL也必须修改索引，因为在 PostgreSQL 中进行的 UPDATE 操作会创建一个新的行版本，因此需要一个新的索引条目来指向表中该行的新位置。

由于这一点比较不幸，因此有一种优化叫做 “HOT update”：如果没有修改索引列 并且包含原始行的块中有足够的空闲空间，则 PostgreSQL 可以创建一个“仅堆积元组(heap-only tuple)”，该元组不会被外部引用，因此不需要新的索引条目。

您可以降低表上的 fillfactor 值，以增加进行 HOT 更新的可能性。

有关详细信息，您可以阅读我关于该主题的文章。

- Laurenz Albe

---

谢谢您的回复，Albe先生！ 我读了您的文章，它让我重新考虑了我的设计。因此，在我的情况下，由于物理存储与性能同样重要，我将尝试避免频繁更新。 - Salih Kavaf

在PostgreSQL中避免不必要的更新是一个明智的举措。 - Laurenz Albe

Answer 2

Laurenz Albe的回答很好。以下是我的解释。
因为gin array_ops不能进行仅索引扫描。这意味着即使您只查询数组列，也只能使用位图索引扫描。对于位图扫描，如果填充因子较低，您可能不需要访问提取页面。

演示：

begin;
create table test_gin_update(cola int, colb int[]);
insert into  test_gin_update values (1,array[1,2]);
insert into  test_gin_update values (1,array[1,2,3]);
insert into  test_gin_update(cola, colb) select g, array[g, g + 1] from generate_series(10, 10000) g;
commit;

例如，执行以下查询：select colb from test_gin_update where colb = array[1,2];，查看下面的查询计划。 由于GIN无法区分array[1,2]和array[1,2,3]，即使我们创建了GIN索引create index on test_gin_update using gin(colb array_ops );，我们仍然只能使用位图索引扫描。

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on test_gin_update (actual rows=1 loops=1)
   Recheck Cond: (colb = '{1,2}'::integer[])
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on test_gin_update_colb_idx (actual rows=2 loops=1)
         Index Cond: (colb = '{1,2}'::integer[])
(6 rows)

- jian

---

感谢指出如此重要的细节！ - Salih Kavaf

那么，假设我的数组列具有类似于您示例的共享值，您认为GIN对于这种用例是无用的吗？ - Salih Kavaf

GIN很有用。因为它仍然比序列扫描快。显然，维护索引仍然需要成本。 - jian