Message-ID: <66028fd5cc301f5726d759e46903f88f8c18217f.camel@cybertec.at>
Subject: Re: Disabling indexes on a table
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: John Scalia <jayknowsunix@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Fri, 19 Feb 2021 10:23:36 +0100
In-Reply-To: <8D9636C9-D38E-4857-A542-1469A11A25FF@gmail.com>
References: <8D9636C9-D38E-4857-A542-1469A11A25FF@gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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