Skip site navigation (1) Skip section navigation (2)

INSERT performance

From: Razvan Surdulescu <surdules(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INSERT performance
Date: 2003-10-30 04:41:04
Message-ID: AD0ob.8977$4k5.8094@twister.austin.rr.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

Thanks,

Razvan.


pgsql-general by date

Next:From: Dave PageDate: 2003-10-30 08:30:31
Subject: Re: [GENERAL] pgAdmin vs SQL Server Enterprise Manager
Previous:From: tjDate: 2003-10-30 04:25:39
Subject: duplicates rejected by index reports PGSQL_FATAL_ERROR

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group