Re: [HACKERS] Postgres Speed or lack thereof

Tom Lane wrote:
>
> > Try to use BEGIN/END to run all inserts in single transaction
> > and please let us know results.
>
> I tried this myself and found that wrapping BEGIN/END around a series of
> INSERT statements didn't make much difference at all.
>
> On an HP 715 (75MHz RISC box, not very heavy iron by current standards),
> I inserted about 13000 records into an initially-empty table having 38
^^
> columns (just because it's data I had handy...). I timed it at:
^^^^^^^
So much.

> Individual INSERT statements (as generated by pg_dump -d):
> 33 inserts/sec
> Same with BEGIN/END wrapped around 'em:
> 34 inserts/sec
> Single COPY statement (as generated by pg_dump without -d):
> 1400 inserts/sec
>
...
>
> John didn't say what hardware he's using, so I don't know how comparable
> my result is to his 150 inserts/sec --- that might have been on a table
> with many fewer columns, or maybe his machine is just faster.
>
> As for where the time is going: "top" showed that the time for the
> INSERT ops was almost all going into backend CPU time. My guess is
> that most of the cost is in parsing and planning the INSERT statements.
> Pre-planned statement skeletons might help, but I think the real answer
> will be to find a way to avoid constructing and parsing SQL statements
> for simple operations like INSERT. (One reason I'm interested in the
> idea of a CORBA interface is that it might help here.)
>
> My guess is that Oracle and mySQL have APIs that allow the construction
> of an SQL INSERT command to be bypassed, and that's why they show up
> better on this operation.

For the table with single int4 column & 2000 INSERTs I have:

BEGIN/END: 3.5sec ~600insert/sec
Without: 151sec ~13insert/sec

(IDE disk!)

I guess that you don't use -F flag..?

Vadim