As i dont know anything about triggers:)
Have you done a vacuum on the databases to clear out old transactions?
/Johan
Michal Samek <webmaster@tony.cz> on 99-05-26 10:47:43
To: pgsql-general@postgreSQL.org
cc: (bcc: Johan Granlund/Solid AB)
Subject: [GENERAL] So slow inserts... Why?
Hi. At first sorry of my English :) I'm using Postgresql in the music
e-shop; the main table kat (katalogue of CDs and MCs) is filled from
.dbf files by a php script. And it's just very, very slow - about 1
inserted record per sec.
Table kat has one primary index and three another indexes, and it's
triggered both on insert and update by plpgsql function to track updates
/ inserts to mark rows which needs updating to production database on
internet. It's no php slowing the process, only postgresql backend loads
processor very much (about 50% on Celeron 266 with 64 MB ram). So I can
understand that it's a time-consuming to insert row, run the trigger
(which is only several lines of the code) and update indicies, but I
thing it's reallt SLOWER THAN IT SHOULD BE.
I've tried MySQL doing the same job, of course without trigger, but it
was TERRIBLY QUICK, for the same job about one minute to process with
Mysql - with postgresql it lasted about 45 min. I do now that Mysql is
considered rather quick, but so much difference? I can't believe that
postgresql is so slow, rather I'm thinking about how to optimize my job.
So, if you are smart enough and want to help me, please, give me some
information about how to affect the speed of inserting. I dno't know,
maybe is better to drop non-primary key indexes, to do inserts and then
re-create indexes? Or to forget using triggers or optimize the backend
some way? Of course I can provide you with details about my querries and
data structures, but I think I'm not doing any special job.
And, postgresql is 6.4.2.
Many thanks for any ideas.
--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210
