Does postgresql update index even when the updated columns aren't the indexed ones?

user_12933115 0
Viewed 589

I want to index an array column with either GIN or GiST. The fact that GIN is slower in insert/update operations, however, made me wonder if it would have any impact on performance - even though the indexed column itself will remain static. So, assuming that, for instance, I have a table with columns (A, B, C) and that B is indexed, does the index get updated if I update only column C?

sql-update indexing postgresql database
2 Answers

It depends :^)

Normally, PostgreSQL will have to modify the index, even if nothing changes in the indexed column, because an UPDATE in PostgreSQL creates a new row version, so you need a new index entry to point to the new location of the row in the table.

Since this is unfortunate, there is an optimization called “HOT update”: If none of the indexed columns are modified and there is enough free space in the block that contains the original row, PostgreSQL can create a “heap-only tuple” that is not referenced from the outside and therefore does not require a new index entry.

You can lower the fillfactor on the table to increase the likelihood for HOT updates.

For details, you may want to read my article on the topic.

user_64643080

Laurenz Albe answer is great. The following part is my interpretation.
Because the gin array_ops can not do index only scan. Which means that even if you only query the array column, you can only use bitmap index scan. for bitmap scan. with low fillfactor, you probably don't need to visit extract pages.

demo:

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;

for example, select colb from test_gin_update where colb = array[1,2]; see the following query plan. because GIN cannot distinguish array[1,2] and array[1,2,3] then even if we created gin index. create index on test_gin_update using gin(colb array_ops ); We can only use bitmap index scan.

                                 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)
user_156034770
Related