Re: bulk insert performance problem

> 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.