From: | Vadim Mikheev <vadim(at)krs(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | John Holland <jholland(at)isr(dot)umd(dot)edu>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Postgres Speed or lack thereof |
Date: | 1999-01-18 04:54:44 |
Message-ID: | 36A2BE94.92FCC294@krs.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-01-18 04:59:55 | Re: [HACKERS] fix pg_dump to dump sequences created by SERIAL datatype |
Previous Message | Bruce Momjian | 1999-01-18 04:51:14 | Re: [HACKERS] drop before create in pg_dump |