postgresql: How to disable all indexes of a table

There are more than 100 indexes on my table. i want to disable all the indexes temporary and want to re-enabled them once the indexing is done.

It is not feasible to go and type every index name to disable it. is there any PostgreSQL statement which can disable all the indexes temporary and then re-enabled them again.

Regards,

From the Comment, you are doing a bulk import using select..

PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).


If you are adding large amounts of data to an existing table, it might
be a win to drop the indexes, load the table, and then recreate the
indexes. Of course, the database performance for other users might
suffer during the time the indexes are missing. One should also think
twice before dropping a unique index, since the error checking
afforded by the unique constraint will be lost while the index is
missing.

You can Check the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

This means that the index won't be used for queries but will still be updated. It's one of the flags used for concurrent index building.
If you set indisready to false, that will disable updates.

If you want to disable all for a single query

BEGIN;
DROP INDEX foo_ndx;
EXPALIN ANALYSE SELECT * FROM foo;
ROLLBACK;

You can do this in a transaction to make recovering from it dead simple. You can also disable indexscan to disable all indices.
Also, make sure you are doing EXPALIN ANALYSE on your queries.

StackExchange Database Administrators Q#190436, answer score: 5