I'm indexing a partitioned table with about 200 million rows, each row is ~1kb in size. I run multiple CREATE INDEX statements sequentially and its pegging the CPU at 100% for 2 hours. Is there a way i can make this less CPU intensive ?
It is worth noting i have increased maintenance_work_mem to ~2GB before executing, so that might've invited more parallelism ?
SHOW max_parallel_maintenance_workers; returns 2
I'm running an db.m6g.large with 2 vCPUs and 8GB memory.
1 Answer 1
To reduce the CPU consumption of CREATE INDEX, the most effective way is to disable parallel query by setting max_parallel_maintenance_workers = 0. Then CREATE INDEX will only use a single CPU core.
To reduce the time that CREATE INDEX takes to build an index (and so to reduce the time during which it consumes CPU), you can allot more memory to it by raising maintenance_work_mem.