Subject: Re: Disabling indexes on a table
To: pgsql-admin@lists.postgresql.org
References: <8D9636C9-D38E-4857-A542-1469A11A25FF@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <9e9e980a-a11e-ad06-2c81-b6f0f7cd8b5f@gmail.com>
Date: Thu, 18 Feb 2021 14:01:35 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <8D9636C9-D38E-4857-A542-1469A11A25FF@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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.