7
Temporarily disable all indexes of a postgresql table
source link: https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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
REINDEX <TABLE_NAME>;
I saved some time with this trick so I just wanted to share it!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK