Search code examples
postgresqldatabase-partitioning

Postgres how do I remove an index on a chilldtable in a partition?

I want to disable an index on a sub partition table. When I try, I get an error. 

ERROR: cannot drop index mysema.col_extra_val_355_customer_id_idx because index mysema.col_extra_val_customer_id_idx1 requires it HINT: You can drop index mysema.col_extra_val_customer_id_idx1 instead.

The table is a huge table, that is partitioned with 1/2 billion rows. When inserting new data, having indexes causes a slow insert. The inserts is clustered for a sub partition table each time. To increase the insert speed, I woluld like to remove the index for that table and then reinstall the index.

Removing the index in the full table will take too much time, as there is 1/2 of billion rows. I use Postgres 11.

Solution

  • You can detach the partition, drop the index, load into the partition, re-create the index and re-attach the partition.

    Of course that only works well if you don't need the partition attached while you are loading.