Postgres Professional
   

Re: batch inserts are "slow"

From: Tim Terlegård
Subject: Re: batch inserts are "slow"
Date: ,
Msg-id: Pine.LNX.4.44.0505021711050.7150-100000@naskur.se.linux.org
(view: Whole thread, Raw)
In response to: batch inserts are "slow"  (Tim Terlegård)
Responses: Re: batch inserts are "slow"  (Christopher Petrilli)
List: pgsql-performance

Tree view

batch inserts are "slow"  (Tim Terlegård, )
 Re: batch inserts are "slow"  (Christopher Kings-Lynne, )
 Re: batch inserts are "slow"  (Christopher Petrilli, )
  Re: batch inserts are "slow"  (Tim Terlegård, )
   Re: batch inserts are "slow"  (Steve Wampler, )
   Re: batch inserts are "slow"  (Christopher Petrilli, )
 Re: batch inserts are "slow"  (Tim Terlegård, )
  Re: batch inserts are "slow"  (Christopher Petrilli, )
 Re: batch inserts are "slow"  (Tom Lane, )
 Re: batch inserts are "slow"  ("David Parker", )
  Re: batch inserts are "slow"  (Markus Schaber, )
   Re: batch inserts are "slow"  (Josh Berkus, )
    Re: batch inserts are "slow"  (Kris Jurka, )
     Re: batch inserts are "slow"  (Dave Cramer, )
   Testing list access  (Jona, )
 Re: batch inserts are "slow"  (Chris Browne, ) 
> > 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?

Tim

pgsql-performance by date:

Previous:
From: Chris Browne
Date:
Subject: Re: batch inserts are "slow"
Next:
From: Markus Schaber
Date:
Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested?