hi...
im on a project using Postgres. The project involves, at times, upto
5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
into a 2 column table (one col=integer, 2nd col=character). I used the
Prepare... and execute method, so i basically had 5M execute statements and
1 prepare statement. Postgres took 144min for this... is there any way to
improve this performance? if so, how? btw, im using it on a SPARC/Solaris
2.6.
thanx in adv
rahul

P.S: Kindly point me towards any relevant documentation as well.


Wed, 13 Aug 2003 10:53:39 +0530
"Rahul_Iyer" <rahul_iyer@persistent.co.in>
Speeding up operations
On Wed, 13 Aug 2003 10:53:39 +0530
"Rahul_Iyer" <rahul_iyer@persistent.co.in> wrote:

> hi...
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M
> inserts into a 2 column table (one col=integer, 2nd col=character). I
> used the Prepare... and execute method, so i basically had 5M execute
> statements and 1 prepare statement. Postgres took 144min for this...
> is there any way to improve this performance? if so, how? btw, im
> using it on a SPARC/Solaris 2.6.
> thanx in adv
> rahul
> 
> P.S: Kindly point me towards any relevant documentation as well.

  If this is a one time insert you'll want to remove any indexes
  and rebuild them after the inserts are done.  

  Also you'll want to look into the COPY command here: 

  http://www.postgresql.org/docs/7.3/static/sql-copy.html

  Loading the data from file like this is probably going to be much
  faster than from a script and/or program. 

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://frank.wiles.org
 ---------------------------------


Wed, 13 Aug 2003 14:47:12 -0500
Frank Wiles <frank@wiles.org>
Re: Speeding up operations
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance? if so, how? btw, im using it on a SPARC/Solaris
> 2.6.

7.4 beta2 will help -- when it comes out.

The fastest method will still be COPY.
Wed, 13 Aug 2003 15:47:56 -0400
Rod Taylor <rbt@rbt.ca>
Re: Speeding up operations
"Rahul_Iyer" <rahul_iyer@persistent.co.in> writes:
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance?

COPY, perhaps.  Have you read
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=populate.html

			regards, tom lane

Wed, 13 Aug 2003 16:26:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Speeding up operations 
Rahul_Iyer kirjutas K, 13.08.2003 kell 08:23:
> hi...
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance? if so, how? btw, im using it on a SPARC/Solaris
> 2.6.

If you are inserting into an empty table with primary key (or other
constraints), you can run ANALYZE on that table in 1-2 minutes after you
have started the INSERTs, so that constraint-checking logic will do the
right thing (use inedex for pk).

in my tests I achieved about 9000 inserts/sec by using multiple
inserting frontends and ~100 inserts per transaction (no indexes, 6
columns, 4 processors, 2GB memory, test clients running on same
computer)

--------------
Hannu


Thu, 14 Aug 2003 01:42:44 +0300
Hannu Krosing <hannu@tm.ee>
Re: Speeding up operations
----- Original Message ----- 
From: "Hannu Krosing" <hannu@tm.ee>
> If you are inserting into an empty table with primary key (or other
> constraints), you can run ANALYZE on that table in 1-2 minutes after you
> have started the INSERTs, so that constraint-checking logic will do the
> right thing (use inedex for pk).

This is one of the things that pg_autovaccum attempts to do.  If you are
doing a lot of inserts into a table, it will perform periodic analyze
commands.  The threshold for performing analyze is much lower than the
threshold for vacuum.  Also, inserts don't effect the count towards the
vacuum threshold, only the analyze threshold.


Thu, 14 Aug 2003 12:47:25 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: Speeding up operations