Re: batch inserts are "slow"

On 5/2/05, Tim Terlegård <tim(at)se(dot)linux(dot)org> wrote:
> > > Howdy!
> > >
> > > I'm converting an application to be using postgresql instead of oracle.
> > > There seems to be only one issue left, batch inserts in postgresql seem
> > > significant slower than in oracle. I have about 200 batch jobs, each
> > > consisting of about 14 000 inserts. Each job takes 1.3 seconds in
> > > postgresql and 0.25 seconds in oracle. With 200 jobs this means several
> > > more minutes to complete the task. By fixing this I think the
> > > application using postgresql over all would be faster than when using
> > > oracle.
> >
> > Have you tried COPY statement?
>
> I did that now. I copied all 3 million rows of data into a text file and
> executed the COPY command. It takes about 0.25 seconds per job. So that's
> much better. I'm afraid jdbc doesn't support COPY though. But now I know
> what the theoretical lower limit is atleast.
>
> Should it be possible to get anyway nearer 0.25s from my current 1.3s?

My experience says 'no'. What you're likely seeing is the parse
overhead of the setup. When you use COPY (as opposed to \copy), the
postmaster is reading the file directory. There's just a lot less
overhead.

Can you write the files on disk and then kick off the psql process to run them?

Chris

--
| Christopher Petrilli
| petrilli(at)gmail(dot)com