> I have a performance problem with a script that does massive bulk
> insert in 6 tables. When the script starts the performance is really
> good but will degrade minute after minute and take almost a day to
> finish!
Looks like foreign key checks slow you down.
- Batch INSERTS in transactions (1000-10000 per transaction)
- Run ANALYZE once in a while so the FK checks use indexes
- Are there any DELETEs in your script which might hit nonidexed
REFERENCES... columns to cascade ?
- Do you really need to check for FKs on the fly while inserting ?
ie. do you handle FK violations ?
Or perhaps your data is already consistent ?
In this case, load the data without any constraints (and without any
indexes), and add indexes and foreign key constraints after the loading is
finished.
- Use COPY instead of INSERT.
If you use your script to process data, perhaps you could import raw
unprocessed data in a table (with COPY) and process it with SQL. This is
usually much faster than doing a zillion inserts.