Without hyperthreading CPU still not a bottleneck, while I/O is only 10% utilization.
top - 14:55:01 up 27 min, 2 users, load average: 0.17, 0.19, 0.14
Tasks: 614 total, 17 running, 597 sleeping, 0 stopped, 0 zombie
Cpu(s): 73.8%us, 4.3%sy, 0.0%ni, 21.6%id, 0.1%wa, 0.0%hi, 0.1%si, 0.0%st
...
I believe the bottleneck may be that pgsql has fight with it's siblings to update the indexes.
I think that should mostly show up as idle or wait, not as user time. Since your at 75% user already, you couldn't expect more than a 33% improvement by fixing that, assuming that that were the problem.
> Is there a way good way to add probes to check where things are slowing down ?
What happens if you just drop the indexes as an experiment? That should put a hard limit on the amount the indexes can be slowing you down.
I like oprofile to give you good bottom level profile of where CPU time is going. Unfortunately, it will probably just show you "AllocSetAlloc". Also, I don't trust it on virtualized systems, if you are on one of those.
There are many other ways of approaching it, but mostly you have to already have a good theory about what is going on in order know which one to use or to interpret the results, and many of them require you to make custom compiles of the postgres server code.
Cheers,
Jeff
