Nobody has asked what kind of machine this is ???
Hard disks, memory, etc.
What are your relevant settings in postgresql.conf ? Shared buffers, checkpoints, etc.
Also how big are the inserts ? What else is this machine doing ? Is it bare hardware, or a VM ?
On 17 September 2015 at 10:41, Eildert Groeneveld <eildert.groeneveld@xxxxxxxxxxx> wrote:
Thanks for your input!On Do, 2015-09-17 at 11:21 -0300, Matheus de Oliveira wrote: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 thatinserting the first 20000 records takes 29 seconds while inserting lot 20 (i.e. 9*20000 later) takes186.9 sec? after all we are talking only about 200000 records? That take 6 times longer!!odd, anyone has an idea?greetingsEildertRegards,--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@xxxxxxxxxxx web: http://vce.tzv.fal.de ==================================================
