Andreas Wernitznig <andreas@insilico.com> writes:
> I am aware of the performance drawbacks because of indices and
> triggers. In fact I have a trigger and an index on the most populated
> table. It is not possible in my case to remove the primary keys
> during insert, because the database structure and foreign keys
> validate my data during import.
Foreign keys eh?
> The problem is, that sometimes the performance is good, and sometimes
> the database is awfully slow. If it is slow, postgres is eating up
> all CPU time and it takes at least 150 times longer to insert the
> data. I don't know why and what to do against that.
We found some foreign-key-related performance problems not long ago,
and it could be you're happening on another one. However there's not
enough info here to figure it out. I can offer you two alternatives:
1. Compile up the backend with profiling enabled (if you're using gcc
then "make PROFILE=-pg clean all" in src/backend should do the trick).
Collect profiles for both a "normal" and a "slow" run and send them in.
2. Develop a self-contained example that exhibits the problem, and send
it along for someone else to profile.
regards, tom lane