Subject: Improving database load speed

From: [email-masked] (Song W. Koh)
Date: Wed, 31 Mar 93 08:29:07 -0800

POSTGRES Experts:

I am working on a project which requires loading 10 - 20 MB of
electrical measurement data frequenly.  Almost all of the data is
floating point.  I am loading into 7 classes, about 100 instances at a
time.

I am using PQexec("append ....").  

Currently it take over 2 minutes to load a test which is about 700
instances. 

System monitors, show that it is waiting for the disk drive most of
the time.

Here are my questions:

1. Will using "copy" or "copy binary" improve disk access => improved
	speed?

2. Is there any other way to make the disk access more efficient?

Thanks in advance,

------------------------------------------------------------------------
Song Koh			Harris Semiconductor
[email-masked]		MS 62-022
Voice: (407)-724-7085		P. O Box 883 (US mail) 
Fax:   (407)-729-4960		Palm Bay Road (UPS)
				Melbourne, FL 32902-0883
------------------------------------------------------------------------

---

From: Mike Olson <[email-masked]>
Date: Wed, 31 Mar 1993 09:17:10 -0800

> 1. Will using "copy" or "copy binary" improve disk access => improved

yup.

> 2. Is there any other way to make the disk access more efficient?

if you don't want to use copy, you can do

	begin
	append
	append
	append 
	...
	end

which will not force a transaction commit (and synchronous disk write) for
every record added to the db.
					mike

---

From: [email-masked] ("Neil M. McQuaig, III")
Date: Thu, 01 Apr 93 00:11:46 CST

> Currently it take over 2 minutes to load a test which is about 700

Are you perchance using a query containing the operator ~?
If so, is it perchance evaluating to find everything such as 
    where someattr~""\g

I had a similar problem an found that such a qualifier has disasterous
affects on throughput.

nmm

---

From: Mike Olson <[email-masked]>
Date: Wed, 31 Mar 1993 09:17:10 -0800

yup.

	begin
	append
	append
	append 
	...
	end

---

From: [email-masked] ("Neil M. McQuaig, III")
Date: Thu, 01 Apr 93 00:11:46 CST

nmm