Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: John Scalia <jayknowsunix@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Disabling indexes on a table
Date: Fri, 19 Feb 2021 07:10:03 -0500
Message-Id: <81BF8164-61B9-4C51-A592-714FA270A6A6@gmail.com>
References: <66028fd5cc301f5726d759e46903f88f8c18217f.camel@cybertec.at>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
In-Reply-To: <66028fd5cc301f5726d759e46903f88f8c18217f.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Precedence: bulk

Thank you for the confirmation Laurenz, I suspected this would be the situat=
ion, and the developer did drop the indexes prior loading then rebuilt them a=
fterwards per my suggestion.

Sent from my iPad

> On Feb 19, 2021, at 4:23 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote=
:
>=20
> =EF=BB=BFOn Thu, 2021-02-18 at 11:24 -0500, John Scalia wrote:
>> One of my developers asked me about this, and he suggested running the fo=
llowing update:
>>=20
>> UPDATE pg_index SET indisready=3Dfalse
>> WHERE indrelid =3D (select oid from pg_class where release =3D =E2=80=98h=
is_table=E2=80=99);
>>=20
>> I told him it=E2=80=99s never a good idea to update anything in a system c=
atalog by hand,
>> but that I would reach out here for a better opinion. Am I correct that h=
e
>> shouldn=E2=80=99t try this, or is he OK to do this? His table has approxi=
mately 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.
>=20
> https://www.postgresql.org/docs/current/catalog-pg-index.html says:
>=20
> indisready bool
>=20
>  If true, the index is currently ready for inserts. False means the
>  index must be ignored by INSERT/UPDATE operations.
>=20
> 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.
>=20
> The indexes will be useless and can only be dropped.  So you are right
> that that is not a good idea.
>=20
> Yours,
> Laurenz Albe
> --=20
> Cybertec | https://www.cybertec-postgresql.com
>=20