Re: INSERT performance

> -----Original Message-----
> From: Razvan Surdulescu [mailto:surdules(at)yahoo(dot)com]
> Sent: Wednesday, October 29, 2003 8:41 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] INSERT performance
>
>
> I'm running PostgreSQL 7.3.4 on Windows 2000/Cygwin (AMD Athlon XP+
> 1800, 512MB RAM).
>
> I want to insert about 500 records into a table. The table is heavily
> indexed (has about 10-12 indices created on it). The insert
> is performed
> in a transaction block.
>
> If I keep the indices on the table, the insert takes about 12
> seconds.
> If I drop the indices, do the insert, and then re-create the indices,
> the entire process takes about 3-4 seconds.
>
> I am somewhat surprised at both of those performance numbers
> above -- I
> would have expected both of them to be a lot smaller (about
> half of what
> they are). 500 records is not that big of a number! In
> particular, the
> transacted insert without indices should be very fast!
>
> Are these numbers about in range to what I should expect from
> PostgreSQL? I looked through the various performance FAQs and
> I cannot
> use the COPY command since the data needs to be processed first, and
> this can only be done in memory.
>
> Any suggestions/pointers would be much appreciated.

Cygwin?
It will be faster under Linux.

500 records in 12 seconds is about 42/second. Hard to know if that is
good or bad. Is the machine under heavy use? Are the records extremely
long?

You can still use the copy command as an API. It will be faster than
the inserts, but there are (of course) caveats with its use.
http://developer.postgresql.org/docs/postgres/libpq-copy.html

Provide the SQL that defines the table and its indexes.

Provide the cardinality of the table.

Provide the average machine load during the insert operation.

Probably, you can get better answers if you provide more information.

What kind of disk drives do you have on your machine? (More
importantly, where does PostgreSQL data reside?)