pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDisabling indexes on a table
4+ messages / 3 participants
[nested] [flat]
* Disabling indexes on a table
@ 2021-02-18 16:24 John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: John Scalia @ 2021-02-18 16:24 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
—
Jay
Sent from my iPad
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Disabling indexes on a table
@ 2021-02-18 20:01 Ron <ronljohnsonjr@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Ron @ 2021-02-18 20:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Disabling indexes on a table
@ 2021-02-19 09:23 Laurenz Albe <laurenz.albe@cybertec.at>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2021-02-19 09:23 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Disabling indexes on a table
@ 2021-02-19 12:10 John Scalia <jayknowsunix@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: John Scalia @ 2021-02-19 12:10 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.albe@cybertec.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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-02-19 12:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-18 16:24 Disabling indexes on a table John Scalia <jayknowsunix@gmail.com>
2021-02-18 20:01 ` Ron <ronljohnsonjr@gmail.com>
2021-02-19 09:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-02-19 12:10 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox