slow INSERTS :((

On Sun, Jun 16, 2002 at 07:21:12PM +0530, Varun Kacholia wrote:

hi ,
I have migrated from MySql to PostgreSQL and i find that inserts are
extremly slow.The things which used to be done in 10-15 mins in mysql
take 1.5 hrs in pgsql(though i expected double or triple the time but
not 8 times...omg).
I am using pgsql-7.1 with Redhat linux 7.2
Tips to increase and improve performace would be appriciated.

Are you using transactions? Putting it all in one transaction should speed
it up considerably.

begin;
inserts...
commit;

Otherwise, shows us the explain for the query.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Varun Kacholia wrote:

hi ,
I have migrated from MySql to PostgreSQL and i find that inserts are
extremly slow.The things which used to be done in 10-15 mins in mysql
take 1.5 hrs in pgsql(though i expected double or triple the time but
not 8 times...omg).
I am using pgsql-7.1 with Redhat linux 7.2
Tips to increase and improve performace would be appriciated.
thanks for any help

VACUUM ANALYZE
... may speed up your database if you're making lots of changes. You
should do this on a regular basis.

Also, if you post details of your table layouts and your insert operations,
the gurus on the list will be able to give you more specific suggestions.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

hi ,
Actually i am building a high end search engine which requires one time
monsterous INSERTs(done by a single script).This is what takes a looot
of time and i was worried abt it.
I read that setting fsync=false will help (but also since wal logs
are used ... will it help that much?).I am not much concerned abt the
data loses as i already have it backed up(in raw form which the script
parses and inserts in the db ) and am solely
concerned that they get *inserted* in the database.
For search purpose i am creating a couple of indexes also ...
should i drop them initially and create them later?
Isnt the following query supported in postgresql (it is surely useful and
supported in MySQL )
CREATE INDEX t_index ON (NAME(20) , ID);
Mysql users might be knowing NAME(20) that it indexes only on first
20 chars of the field NAME.
Postgresql refused to take this and as a result i have indexes made
on fields whose length is like 200 chars.
any kind of help would be appriciated
thanks

hi ,
I have migrated from MySql to PostgreSQL and i find that inserts are
extremly slow.The things which used to be done in 10-15 mins in mysql
take 1.5 hrs in pgsql(though i expected double or triple the time but
not 8 times...omg).
I am using pgsql-7.1 with Redhat linux 7.2
Tips to increase and improve performace would be appriciated.
thanks for any help

To help, we need to know what you are doing, how you have configured,
&c. Are you running in one transaction? Check the archives for lots
of performance posts.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
+1 416 646 3304 x110

--
------
Varun
Do not kill time ... else time will kill you

Varun Kacholia <varunk@cse.iitb.ac.in> writes:

I have migrated from MySql to PostgreSQL and i find that inserts are
extremly slow.

See the standard tips at
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/populate.html

Isnt the following query supported in postgresql (it is surely useful and
supported in MySQL )
CREATE INDEX t_index ON (NAME(20) , ID);

I can't see any particular value in that myself, but Postgres supports
a generic approach to weird indexing requirements: functional indexes.
See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/indexes-functional.html

regards, tom lane