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
---------------------------------

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.

"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&amp;idoc=0&amp;file=populate.html

regards, tom lane

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

----- 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.