Re: :(( VEEERRRRYYYY SLLLOOOWWWWWW!

----- Original Message -----
From: "Costin Manda" <costin(dot)m(at)interpoint(dot)ro>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, March 28, 2005 7:27 AM
Subject: [NOVICE] :(( VEEERRRRYYYY SLLLOOOWWWWWW!

>I have this computer using postgres and PHP script that access and insert
> values in this database. I also have a computer with a MsSQL db on it. I
> made a script to read and update values from the MsSQL db to postgres.
>
> At the time I had postgres 7.4.1 and the script went excruciatingly slow.
> Something like 4 inserts per second! I tried every optimisation trick I
> could find on the net, but to no avail.
>
> Finally I upgraded to 8.0.1 and the script ran INSTANTLY! like, 200
> thousand
> inserts in 10 minutes. So I was very happy about it. But yesterday the
> power
> went off. When I restarted the computer, the script went very slow again.
>
> I tried vacuuming, reindexing, deleting all indexes and recreating them,
> using pg_resetxlog, changing linux kernel variables and postgres
> configuration variables. Nothing helps!

I'm not sure what this particular issue is (why the change in behavior), but
did you consider changing your code to use "copy" instead of "insert"? The
same 200k rows will probably copy in a few seconds. If you absolutely need
to do the insert, make sure you are "preparing" the SQL and then
"executing".

Sean