Re: Insert Performance

Tom Lane wrote:

> "Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> > To insert another 10562 rows takes about 12 minutes now!!!
>
> > As I said I wrote a function to insert the rows (PL/pgSQL). All values
were
> > inserted inside a single function call; I always though that a function
call
> > would be executed inside a transaction block. Experience says it does.
>
> Well, there's something fishy about your results. Using CVS tip I see
> about a 4-to-1 difference between COPYing 10000 rows and INSERT'ing
> 10000 rows (as one transaction). That's annoyingly high, but it's still
> way lower than what you're reporting ...
>
> I used the contents of table tenk1 in the regression database for test
> data, and dumped it out with "pg_dump -a" with and without -d. I then
> just timed feeding the scripts to psql ...
>
> regards, tom lane

I have further played around with the test here. I now realized that insert
performance is much better right after a vacuum full; vacuum analyze;

I have this function bench_invoice(integer) that will insert $1 records into
invoice table;
select bench_invoice(10000) took about 10 minutes average. Now I executed
this with psql:

vacuum full; vacuum analyze;
select bench_invoice(1000); select bench_invoice(1000); ... (10 times)

It seems performance is degrading with every insert!
Here is the result (time in seconds in bench_invoice(), commit between
selects just under a second)

13, 24, 36, 47, 58, 70, 84, 94, 105, 117, ... (seconds per 1000 rows
inserted)

Isn't that odd?
I have tried again. vacuum analyze alone (without full) is enough to lower
times again. They will start again with 13 seconds.

I did not delete from the table by now; the table now has about 50000 rows.
The disk is not swapping, there are no other users using postgres,
postmaster takes about 100% cpu time during the whole operation. There are
no special messages in error log.

Can you explain?
Should I enable some debug logging? Disable some optimizer? Do something
else?
This is a development server, I habe no problem with playing around.

Best Regards,
Michael Paesold