pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erki Kaldjärv <erki@webware.ee>
To: Bill Moran <wmoran@potentialtech.com>
Cc: Qing Zhao <qzhao@quotefx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: inserting large number of rows was: Re: Increasing
Date: Tue, 03 Feb 2004 09:03:05 +0200
Message-ID: <401F47A9.7070106@webware.ee> (raw)
References: <Pine.LNX.4.33.0402021346040.20085-100000@css120.ihs.com>
	<401EC2FE.3000500@potentialtech.com>

You could do high speed inserts with COPY command:
http://developer.postgresql.org/docs/postgres/sql-copy.html

Check whenether your database adapter/client lib supports it (i guess it 
does).

Note that it doesnt help very much if there are fk's/triggers's on the 
target table.

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 
>> increasing.
>>
>> 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@postgresql.org)
>>
>
>





view thread (3+ messages)

Message-ID: <401F47A9.7070106@webware.ee>
Permalink:  ../401F47A9.7070106@webware.ee/
Also on:    postgresql.org/message-id/401F47A9.7070106@webware.ee

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: erki@webware.ee, wmoran@potentialtech.com, qzhao@quotefx.net
  Subject: Re: inserting large number of rows was: Re: Increasing
  In-Reply-To: <401F47A9.7070106@webware.ee>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox