From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Disabling indexes on a table |
Date: | 2021-02-18 20:01:35 |
Message-ID: | 9e9e980a-a11e-ad06-2c81-b6f0f7cd8b5f@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Holger Jakobs | 2021-02-18 21:39:06 | Re: Error after Streaming Replication |
Previous Message | S Bob | 2021-02-18 18:19:29 | Significantly higher Read IO usage after upgrade from 10.7 to 10.11 |