Thread: Speeding up operations
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.
> 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.
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---------------------------------
"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
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.