I tried to do this.....
What I've done is create a plpgpsql function that does the inserts.
There is a begin/end in the function of course...I also tried doing it
with a begin work before calling the function in psql, and it did seem
that this worked as it should - ie a rollback work would get rid of the
40000 inserts.
Still, it took about 3.5 minutes, and Oracle does it in about 30 seconds.
factor of 7.....
On the plus side for Postgres, trying to find some queries against the
data for other speed tests, I found that it seemed to respond quickly -
not much thinking to do with one table of integers I guess. Actually I
couldn't come up with a query that would take very long - need another
table I suppose.
I found that UPDATE TEST SET A = B -1 took only 20 seconds for 40000
records.
The real plus is that Oracle couldn't do that transaction because it was
running out of rollback segment space - something I have fixed in the past
but I recall being a pain to fix - need to make bigger rollback datafiles
or whatever.
Any suggested speed tests would be appreciated.
>
> Try to use BEGIN/END to run all inserts in single transaction
> and please let us know results.
>
> Vadim
>