From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Alain Reymond <alain.reymond@ceia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: degradation in performance
Date: Tue, 21 Sep 2004 05:25:16 -0700 (PDT)
Message-ID: <20040921051822.D6782@megazone.bigpanda.com> (raw)
In-Reply-To: <41502C4F.15411.6617D37@localhost>
References: <41502C4F.15411.6617D37@localhost>
On Tue, 21 Sep 2004, Alain Reymond wrote:
> I created a database with Postgres 7.3.4 under Linux RedHat 7.3 on a
> Dell PowerEdge server.
You should probably upgrade to the end of the 7.3 branch at the least
(7.3.7).
> 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.
Some questions... Are you doing the inserts each in their own transaction
or are you putting them in a single transaction or batching some number
per transaction? Have you considered using copy for importing large blocks
of data? Is this table basically only taking inserts (no delete or
update)? Does this table have foreign key references to another table or
have any triggers?
You might see if reindexing the table or running vacuum full verbose(*)
helps.
(*) - I don't remember how 7.3 handled cluster, but if vacuum full verbose
says there's lots of removable row entries, clustering the table might be
faster.
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: sszabo@megazone.bigpanda.com, alain.reymond@ceia.com
Subject: Re: degradation in performance
In-Reply-To: <20040921051822.D6782@megazone.bigpanda.com>
* 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