From: | John Scalia <jayknowsunix(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Disabling indexes on a table |
Date: | 2021-02-19 12:10:03 |
Message-ID: | 81BF8164-61B9-4C51-A592-714FA270A6A6@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Thank you for the confirmation Laurenz, I suspected this would be the situation, and the developer did drop the indexes prior loading then rebuilt them afterwards per my suggestion.
Sent from my iPad
> On Feb 19, 2021, at 4:23 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | dbatoCloud Solution | 2021-02-19 12:28:48 | Looking find the each row size of table |
Previous Message | Laurenz Albe | 2021-02-19 09:23:36 | Re: Disabling indexes on a table |