I have a Postgres instance building a GIN index. It's looking at about 200,000 rows and it's so far taken about 9 hours. Who knows how long it will take eventually. The problem is that it's using about 2% of CPU when I'd like it to use more like 90%. Is there any way to force it to speed up?
+1
A:
The main bottleneck is probably disk IO and not CPU.
If you're on a Windows machine, you can check disk IOs using Process Explorer (freeware), if on Unix, use iostat, sar, DTrace (haven't done the latter in a while so not 100% sure of the best tool)
DVK
2010-03-16 21:10:50
The Resource Monitor on Windows also shows this and you can usually easily see there whether the machine is currently CPU-limited or by IO.
Joey
2010-03-16 21:14:47
Joe
2010-03-16 21:51:02
I think it was due to paging, leading to thrashing. The answer was to increase the working memory by `set maintenance_work_mem = 102400;`.
Joe
2010-03-24 11:33:57