pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Frank Morton <fmorton@base2inc.com>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Slow Inserts Again
Date: Mon, 3 May 1999 16:50:09 +0300
Message-ID: <l03130305b35358ca72f2@[147.233.159.109]> (raw)
In-Reply-To: <06b501be9567$e5941590$8355e5ce@base2inc.com>

At 16:21 +0300 on 03/05/1999, Frank Morton wrote:


> I have tried many combinations of things to speed this
> up as you all have suggested. I have had no success
> using "copy" at all because of problems with quotes
> and other punctuation in the data.

I must tell you, this doesn't sound reasonable to me. It's usually very
easy, if you already have a program that writes down the fields, to make
sure it scans the contents thereof and adds a backslash before each tab,
newline and backslash in every one of the fields.

If what you need is to dump-reload, then this is exactly what the dump
program will do for you.

> This last attempt, I bracket each insert statement with
> "begin;" and "end;".

Not necessary. An insert has an implicit begin-end around it. If you want
to change that behaviour, group several inserts together in begin-end.
Putting them individually is redundant.

> What I am seeing this time around is in the beginning,
> the inserts were reasonable in speed. Say 6 or 7
> per second. But now that it is up to record 100,000 or
> so (3 DAYS later) the time between inserts is about
> 10 SECONDS. As progress is made, the inserts
> continue to get slower and slower. So at the current
> rate, I have another 138 hours before completion!

Perhaps you need to break the connection. That is, every 1000 records,
disconnect from the backend, and connect again. A vacuum every so often may
also help somewhat.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (16+ messages)  latest in thread

Message-ID: <l03130305b35358ca72f2@[147.233.159.109]>
Permalink:  ../l03130305b35358ca72f2@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130305b35358ca72f2@[147.233.159.109]

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, fmorton@base2inc.com, pgsql-sql@hub.org
  Subject: Re: [SQL] Slow Inserts Again
  In-Reply-To: <l03130305b35358ca72f2@[147.233.159.109]>
  References: <06b501be9567$e5941590$8355e5ce@base2inc.com> <l03130305b35358ca72f2@[147.233.159.109]>

* 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