To: "Alain Reymond" <alain.reymond@ceia.com>,
	pgsql-sql@postgresql.org
Subject: Re: degradation in performance
References: <41502C4F.15411.6617D37@localhost>
Message-ID: <opseof7gzycq72hf@musicbox>
Date: Tue, 21 Sep 2004 14:41:30 +0200
From: =?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?=
	<lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <41502C4F.15411.6617D37@localhost>
User-Agent: Opera M2/7.53 (Linux, build 737)


	6000 inserts, each in its own transaction, will be very long.
	Group your inserts in one transaction and it'll be faster (maybe 1-2  
minutes).
	Have your program generate a tab-delimited text file and load it with  
COPY, you should be down to a few seconds.


On Tue, 21 Sep 2004 13:27:43 +0200, Alain Reymond <alain.reymond@ceia.com>  
wrote:

> Good afternoon,
>
> I created a database with Postgres 7.3.4 under Linux RedHat 7.3 on a
> Dell PowerEdge server.
>
> One of the table is
> resultats(numbil, numpara, mesure, deviation)
> with an index on numbil.
>
> Each select on numbil returns up to 60 rows (that means 60 rows for
> one numbil with 60 different numpara) for example
> (200000,1,500,3.5)
> (200000,2,852,4.2)
> (200000,12,325,2.8)
> (200001,1,750,1.5)
> (200001,2,325,-1.5)
> (200001,8,328,1.2)
> etc..
>
> This table contains now more than 6.500.000 rows and grows from
> 6000 rows a day. I have approximatively 1.250.000 rows a year. So I
> have 5 years of data online.
> Now, an insertion of 6000 lasts very loooong, up to one hour...
> I tried to insert 100.000 yesterday evening and it was not done in 8
> hours.
>
> Do you have any idea how I can improve speed - apart from splitting
> the table every 2 or 3 years which is the the aim of a database!
>
> I thank you for your suggestions.
>
> Regards.
>
> Alain Reymond
> CEIA
> Bd Saint-Michel 119
> 1040 Bruxelles
> Tel: +32 2 736 04 58
> Fax: +32 2 736 58 02
> alain.reymond@ceia.com
> PGP key sur http://pgpkeys.mit.edu:11371
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>