INSERT performance problems
Posted in 1999
Hello all. I'm having some performance problems at my site, and I'm hoping some of you in the newsgroup will have suggestions that can help. The problem is that I've got a huge accounts receivable table (1.6 million rows), into which I need to be able to do large inserts (hundreds of thousands of rows) for our end-of-period posting. These inserts take several hours to complete. My window is more like two hours. Having done some initial analysis, I know that indexes and constraints on the A/R table are causing a good deal of the trouble. With constraints and indexes disabled, the inserts take one third of the time; but to re-enable those indexes and constraints takes even longer than if I had just left them enabled. Here are some key statistics: # columns: 50 rowsize: 471 bytes idxsize: ~212 bytes special columns: 3 primary key: (serial), referenced by two other tables foreign keys: 17 (I know, that's a lot) The table is fragmented across four dbspaces (equivalent with the number of high-performance disks we have), and the indexes are detached, on two other disks. Any suggestions will be greatly appreciated.