pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
inserting large number of rows was: Re: Increasing number of PG connections.
3+ messages / 3 participants
[nested] [flat]

* inserting large number of rows was: Re: Increasing number of PG connections.
@ 2004-02-02 20:49  scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: scott.marlowe @ 2004-02-02 20:49 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; +Cc: Kevin Barnard <kbarnard@speedfc.com>; pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: inserting large number of rows was: Re: Increasing
@ 2004-02-02 21:37  Bill Moran <wmoran@potentialtech.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Bill Moran @ 2004-02-02 21:37 UTC (permalink / raw)
  To: ; +Cc: Qing Zhao <qzhao@quotefx.net>; pgsql-performance

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)
> 


-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: inserting large number of rows was: Re: Increasing
@ 2004-02-03 07:03  Erki Kaldjärv <erki@webware.ee>
  parent: Bill Moran <wmoran@potentialtech.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Erki Kaldjärv @ 2004-02-03 07:03 UTC (permalink / raw)
  To: Bill Moran <wmoran@potentialtech.com>; +Cc: Qing Zhao <qzhao@quotefx.net>; pgsql-performance

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)
>>
>
>





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-02-03 07:03 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-02 20:49 inserting large number of rows was: Re: Increasing number of PG connections. scott.marlowe <scott.marlowe@ihs.com>
2004-02-02 21:37 ` Re: inserting large number of rows was: Re: Increasing Bill Moran <wmoran@potentialtech.com>
2004-02-03 07:03   ` Re: inserting large number of rows was: Re: Increasing Erki Kaldjärv <erki@webware.ee>

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