From: Eildert Groeneveld <eildert.groeneveld@fli.bund.de>
To: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: Ladislav Lenart <lenartlad@volny.cz>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: degrading inser performance
Date: Thu, 17 Sep 2015 16:41:08 +0200
Message-ID: <1442500868.4959.35.camel@fli.bund.de> (raw)
In-Reply-To: <CAJghg4KWkUhkzNeuqkihDCxVBgseJDeCoYni735RwiUOad4-5g@mail.gmail.com>
References: <1442489550.4959.12.camel@fli.bund.de>
<55FAAE0B.9020506@volny.cz>
<1442492365.4959.18.camel@fli.bund.de>
<CAJghg4KWkUhkzNeuqkihDCxVBgseJDeCoYni735RwiUOad4-5g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Thanks for your input!
On Do, 2015-09-17 at 11:21 -0300, Matheus de Oliveira wrote:
>
> On Thu, Sep 17, 2015 at 9:19 AM, Eildert Groeneveld <
> eildert.groeneveld@fli.bund.de> wrote:
> > > * one COPY per bulk (20 000 rows)
> > copy does not fit so well, as it is not only initial populating.
> >
> Why do you say COPY doesn't fit? It seems to me that COPY fits
> perfectly for your case, and would certainly make the load faster.
well, more than one table needs to get populated and data is not really
available in one file.
> I suspect (not sure though) that the degradation is most because you are inserting one row at a time, and, it needs to verify FSM (Free Space Map) for each tuple inserted, when the table start to get more populated, this verification starts to become slower. If that is really the case, COPY would certainly improve that, or even INSERT with many rows at once.
allright, sounds reasonable.
But what is your experience: is it possible that
inserting the first 20000 records takes 29 seconds while inserting lot
20 (i.e. 9*20000 later) takes
186.9 sec? after all we are talking only about 200000 records? That
take 6 times longer!!
odd, anyone has an idea?
greetings
Eildert
> > Regards,> --
> Matheus de Oliveira>
>
>
>
--
Eildert Groeneveld
===================================================
Institute of Farm Animal Genetics (FLI)
Mariensee 31535 Neustadt Germany
Tel : (+49)(0)5034 871155 Fax : (+49)(0)5034 871143
e-mail: eildert.groeneveld@fli.bund.de
web: http://vce.tzv.fal.de
==================================================
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-performance@postgresql.org
Cc: eildert.groeneveld@fli.bund.de, matioli.matheus@gmail.com, lenartlad@volny.cz
Subject: Re: degrading inser performance
In-Reply-To: <1442500868.4959.35.camel@fli.bund.de>
* 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