Temporarily disable all indexes of a postgresql table
When you run a large query (insert/update) on a huge table with several indexes, these indexes can seriously slow the query execution.
With Postgresql it can be very faster to disable the indexes before runing the query and reindex all the table afterwards.
You can do it like this :
- Disable all table indexes
UPDATE pg_index SET indisready=false WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname='<TABLE_NAME>' );
- Run your query
UPDATE <TABLE_NAME> SET ...;
- Reenable all table indexes
UPDATE pg_index SET indisready=true WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname='<TABLE_NAME>' );
- Reindex table
I saved some time with this trick so I just wanted to share it!