Re: [GENERAL] So slow inserts... Why? - Mailing list pgsql-general

From johan.granlund@solid.se
Subject Re: [GENERAL] So slow inserts... Why?
Date
Msg-id C125677D.004C5924.00@mail.solid.se
Whole thread Raw
List pgsql-general
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






pgsql-general by date:

Previous
From: Chris Bitmead
Date:
Subject: Deadlock problems
Next
From: Adriaan Joubert
Date:
Subject: Weird error message when loading PL functions