Tom Lane wrote:
> "Michael Paesold" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
