CODEWITHSUNDEEP
sqlpostgresqlindexingcpu
Mahoni
Mahoni

Reputation: 7466

Partial index creation takes the CPU down

I am trying to create an index on a table with a couple of million entries. Unfortunately, whenever I try it, the CPU goes up and I have to kill it around 90% CPU utilization, because otherwise it would harm production.

What can I do to create the index then? It's a partial index. I have already set maintenance_work_mem to 2GB. I can't really change checkpoint_segments while the database is running. CREATE INDEX CONCURRENTLY would take the database just down quicker.

So what else could I do?

Upvotes: 0

Views: 51

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

Index creation certainly can't hit 90% CPU on a modern multicore system (mainly because it's using just one core). What's more likely you're blocking all the queries against the table. Please try building the index CONCURRENTLY (manual).

Upvotes: 1

Related Questions