Обсуждение: Disabling indexes on a table
One of my developers asked me about this, and he suggested running the following update: UPDATE pg_index SET indisready=false WHERE indrelid = (select oid from pg_class where release = ‘his_table’); I told him it’s never a good idea to update anything in a system catalog by hand, but that I would reach out here for a betteropinion. Am I correct that he shouldn’t try this, or is he OK to do this? His table has approximately 8 different indexeson it, and those really slow down his bulk loads. Usually when I have to get involved, I just drop his indexes andrebuild them afterwards, and I know that is always safe. — Jay Sent from my iPad
On 2/18/21 10:24 AM, John Scalia wrote: > One of my developers asked me about this, and he suggested running the following update: > > UPDATE pg_index SET indisready=false > WHERE indrelid = (select oid from pg_class where release = ‘his_table’); He found that on the Internet: https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > I told him it’s never a good idea to update anything in a system catalog by hand, but that I would reach out here for abetter opinion. Am I correct that he shouldn’t try this, or is he OK to do this? His table has approximately 8 differentindexes on it, and those really slow down his bulk loads. Usually when I have to get involved, I just drop his indexesand rebuild them afterwards, and I know that is always safe. Disabling indices "just" saves having to run X number of CREATE INDEX statements, which is nothing to sneeze at since it's one less place to have to remember to update: "REINDEX <table>;" just handles it all. Just as importantly, the index you want to drop might be a PK supporting a FK. (Postgresql really needs ALTER INDEX ... DISABLE and ALTER TABLE ... DISABLE ALL INDEXES;" statements.) -- Angular momentum makes the world go 'round.
On Thu, 2021-02-18 at 11:24 -0500, John Scalia wrote: > One of my developers asked me about this, and he suggested running the following update: > > UPDATE pg_index SET indisready=false > WHERE indrelid = (select oid from pg_class where release = ‘his_table’); > > I told him it’s never a good idea to update anything in a system catalog by hand, > but that I would reach out here for a better opinion. Am I correct that he > shouldn’t try this, or is he OK to do this? His table has approximately 8 > different indexes on it, and those really slow down his bulk loads. > Usually when I have to get involved, I just drop his indexes and rebuild > them afterwards, and I know that is always safe. https://www.postgresql.org/docs/current/catalog-pg-index.html says: indisready bool If true, the index is currently ready for inserts. False means the index must be ignored by INSERT/UPDATE operations. So that would indeed speed up a bulk load, but your indexes would be unusable afterwards. If you run a second update to "pg_index" to set "indisready" to TRUE, you would end up with corrupted indexes. The indexes will be useless and can only be dropped. So you are right that that is not a good idea. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thank you for the confirmation Laurenz, I suspected this would be the situation, and the developer did drop the indexes priorloading then rebuilt them afterwards per my suggestion. Sent from my iPad > On Feb 19, 2021, at 4:23 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Thu, 2021-02-18 at 11:24 -0500, John Scalia wrote: >> One of my developers asked me about this, and he suggested running the following update: >> >> UPDATE pg_index SET indisready=false >> WHERE indrelid = (select oid from pg_class where release = ‘his_table’); >> >> I told him it’s never a good idea to update anything in a system catalog by hand, >> but that I would reach out here for a better opinion. Am I correct that he >> shouldn’t try this, or is he OK to do this? His table has approximately 8 >> different indexes on it, and those really slow down his bulk loads. >> Usually when I have to get involved, I just drop his indexes and rebuild >> them afterwards, and I know that is always safe. > > https://www.postgresql.org/docs/current/catalog-pg-index.html says: > > indisready bool > > If true, the index is currently ready for inserts. False means the > index must be ignored by INSERT/UPDATE operations. > > So that would indeed speed up a bulk load, but your indexes would be > unusable afterwards. If you run a second update to "pg_index" to > set "indisready" to TRUE, you would end up with corrupted indexes. > > The indexes will be useless and can only be dropped. So you are right > that that is not a good idea. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >