Should I REINDEX table after large update on column not concerned by the indexes

user_3871985 0
Viewed 606

To speed up an update on a large postgresql table, I disable the indexes :

UPDATE pg_index SET indisready=false WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname='mytable')

Then run my update :

update mytable set ......

Then enable my indexes :

UPDATE pg_index SET indisready=true WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname='mytable');

Then reindex :

REINDEX table mytable;

Is the last reindex necessary considering that my update is about columns not concerned by my indexes ?

Thanks for your help.

indexing postgresql
1 Answers

This is a recipe for data corruption.

It will only work if all your updates happen to be HOT updates, because only in that case the ctid of the row (the physical address which is referenced by the index) remains the same.

If you have to mess with the catalogs, then only disable those indexes that you are going to REINDEX later. But it would be much better to just drop these indexes and create them again. That way you don't have to mess with the catalogs and run no risk of data corruption.

See the source documentation for details about HOT.

user_64643080
Related