我想要使用 GIN 或 GiST 对一个数组列进行索引。然而,GIN 在插入/更新操作中速度较慢,这让我想知道它是否会对性能产生影响 - 即使被索引的列本身保持静态不变。
因此,假设我有一张包含列(A,B,C)的表格,并且B已经建立了索引,如果我只更新了列C,那么索引会被更新吗?
这要看情况: ^)
通常情况下,即使索引列中没有任何更改,PostgreSQL也必须修改索引,因为在 PostgreSQL 中进行的 UPDATE
操作会创建一个新的行版本,因此需要一个新的索引条目来指向表中该行的新位置。
由于这一点比较不幸,因此有一种优化叫做 “HOT update”:如果没有修改索引列 并且包含原始行的块中有足够的空闲空间,则 PostgreSQL 可以创建一个“仅堆积元组(heap-only tuple)”,该元组不会被外部引用,因此不需要新的索引条目。
您可以降低表上的 fillfactor
值,以增加进行 HOT 更新的可能性。
有关详细信息,您可以阅读我关于该主题的文章。
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)