From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: Alain Reymond <alain.reymond@ceia.com>
To: pgsql-sql@postgresql.org
Subject: Re: degradation in performance
Date: Tue, 21 Sep 2004 14:41:30 +0200
Message-ID: <opseof7gzycq72hf@musicbox> (raw)
In-Reply-To: <41502C4F.15411.6617D37@localhost>
References: <41502C4F.15411.6617D37@localhost>
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
>
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-sql@postgresql.org
Cc: lists@boutiquenumerique.com, alain.reymond@ceia.com
Subject: Re: degradation in performance
In-Reply-To: <opseof7gzycq72hf@musicbox>
* 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