On Friday, January 11, 2013, Horst Dehmer wrote:
Except - and that's the wall I'm hitting - for one table which yielded just 75 records/second.
The main 'problem' seem to be the FK constraints. Dropping just them restored insert performance for this table to 6k records/s.
It sure sounds like you don't have enough RAM to hold the foreign-key table data needed to check the constraints, so every insert needs one disk revolution to fetch the data.
If you drop the indexes and constraints one at a time until it speeds up, is there a certain one that is the culprit?
You can look in pg_statio_user_tables to see what tables and indexes have high io being driven by the bulk loading.
Use "top" to see of the server is mostly IO bound or CPU bound.
Cheers,
Jeff