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

Re: poor performance of loading data

From: Chris Ruprecht <chrup999(at)yahoo(dot)com>
To: "Zhang, Anna" <azhang(at)verisign(dot)com>
Cc: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: poor performance of loading data
Date: 2001-12-20 00:55:06
Message-ID: p05101001b846e621b81d@[] (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

At 12:57 -0500 12/19/2001, Zhang, Anna wrote:
>I just installed Postgres 7.1.3 on my Red Hat 7.2 linux box. We are doing
>research to see how postgres doing, I used copy utility to import data from
>a text file which contains 32 mils rows, it has been 26 hours passed, but
>still running. My question is how postgres handles such data loading? it
>commited every row? or commit point is adjustable? How? Does postgres
>provide direct load to disk files like oracle? Other ways to speed up? If
>loading performance can't be improved significantly, we have to go back to
>oracle. Anybody can help? Thanks!

Did you edit the PostGreSQL startup parameters? if not, you have a 
very sluggish system. You should have an ample supply of shared 
memory (2000 - 8000 blocks of 8K each). Also, do you have an index 
defined on your table? If so, drop the index, load the records, then, 
re-create the index(es) for the table. Speed will improve 
tremendously for copy commands with no index to update. creating an 
index for all 32M records is much faster than having to insert index 

You might find your file loaded in a fraction of the time is has used 
already with these optimizations applied. Without knowing more 
specifics, this is all I can recommend.

Best regards,
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

Do You Yahoo!?
Get your free address at

In response to


pgsql-admin by date

Next:From: Ravindra WankarDate: 2001-12-20 07:02:02
Subject: Data partitioning
Previous:From: Michael T. HalliganDate: 2001-12-19 23:52:47
Subject: Re: [Tuning questions..]

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