Re: Re: low performance

I took option 1 and managed to create a profile of a slow and a fast run:

The frequent functions of the FAST run:

% cumulative self self total
time seconds seconds calls Ts/call Ts/call name
0.00 0.00 0.00 15725437 0.00 0.00 AllocSetAlloc
0.00 0.00 0.00 15380742 0.00 0.00 MemoryContextAlloc
0.00 0.00 0.00 11296700 0.00 0.00 ExecEvalExpr
0.00 0.00 0.00 8276639 0.00 0.00 newNode
0.00 0.00 0.00 5430717 0.00 0.00 MemoryContextSwitchTo
0.00 0.00 0.00 4492641 0.00 0.00 LockBuffer
0.00 0.00 0.00 4425642 0.00 0.00 AllocSetFree
0.00 0.00 0.00 4356571 0.00 0.00 pfree
0.00 0.00 0.00 3873174 0.00 0.00 pq_getbyte
0.00 0.00 0.00 3799725 0.00 0.00 appendStringInfoChar

The frequent functions of the SLOW run:

% cumulative self self total
time seconds seconds calls Ts/call Ts/call name
0.00 0.00 0.00 27832819 0.00 0.00 ExecEvalExpr
0.00 0.00 0.00 19040887 0.00 0.00 AllocSetAlloc
0.00 0.00 0.00 18976313 0.00 0.00 MemoryContextAlloc
0.00 0.00 0.00 18722462 0.00 0.00 LockBuffer
0.00 0.00 0.00 18684832 0.00 0.00 MemoryContextSwitchTo
0.00 0.00 0.00 18442039 0.00 0.00 pg_detoast_datum
0.00 0.00 0.00 16947638 0.00 0.00 AllocSetFree
0.00 0.00 0.00 16934648 0.00 0.00 pfree
0.00 0.00 0.00 9716164 0.00 0.00 SpinAcquire
0.00 0.00 0.00 9716164 0.00 0.00 SpinRelease

Since these files are to big for a posting, I have put the whole profile files on:
ftp://ftp.insilico.com/out.fast.gz
ftp://ftp.insilico.com/out.slow.gz

I don't know why the time column and number of seconds is zero in all the cases.
I am using the Redhat 7.1 binutils (binutils-2.10.91.0.2-3).

On Tue, 21 Aug 2001 17:38:23 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andreas Wernitznig <andreas(at)insilico(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>