You could do high speed inserts with COPY command:
Check whenether your database adapter/client lib supports it (i guess it
Note that it doesnt help very much if there are fk's/triggers's on the
Bill Moran wrote:
> I must have missed this post when it was made earlier. Pardon the
> noise if
> my suggestion has already been made.
> Unlike MySQL (and possibly other database servers) PostgreSQL is
> faster when
> inserting inside a transaction. Depending on the method in which you are
> actually adding the records.
> In my own experience (generating a list of INSERT statements from a perl
> script and using psql to execute them) the difference in performance was
> incredibly dramatic when I added a "BEGIN WORK" at the beginning and
> "COMMIT WORK" at the end.
> scott.marlowe wrote:
>> On Mon, 2 Feb 2004, Qing Zhao wrote:
>>> I am new here. I have a question related to this in some way.
>>> Our web site needs to upload a large volume of data into Postgres at
>>> a time. The performance deterioates as number of rows becomes
>>> larger. When it reaches 2500 rows, it never come back to GUI. Since
>>> the tests were run through GUI, my suspision is
>>> that it might be caused by the way the application server talking to
>>> Postgres server, the connections, etc.. What might be the factors
>>> involved here? Does anyone know?
>> Actually, I'm gonna go out on a limb here and assume two things:
>> 1. you've got lotsa fk/pk relationships setup.
>> 2. you're analyzing the table empty before loading it up.
>> What happens in this instance is that the analyze on an empty, or
>> nearly so, table, means that during the inserts, postgresql thinks
>> you have only a few rows. At first, this is fine, as pgsql will seq
>> scan the tables to make sure there is a proper key in both. As the
>> number of rows increases, the planner needs to switch to index scans
>> but doesn't, because it doesn't know that the number of rows is
>> Fix: insert a few hundred rows, run analyze, check to see if the
>> explain for inserts is showing index scans or not. If not, load a
>> few more hundred rows, analyze, rinse, repeat.
>> Also, look for fk/pk mismatches. I.e. an int4 field pointing to an
>> int8 field. That's a performance killer, so if the pk/fk types don't
>> match, see if you can change your field types to match and try again.
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
In response to
pgsql-performance by date
|Next:||From: Czuczy Gergely||Date: 2004-02-03 07:25:38|
|Subject: PQexecParams and types|
|Previous:||From: Christopher Kings-Lynne||Date: 2004-02-03 05:52:41|
|Subject: Re: MySQL+InnoDB vs. PostgreSQL test?|