Slow Inserts in PostgreSQL

I have a problem when doing INSERT's in a table.

The table structure is:

uri (varchar 10000) PK
id_language (varchar 10) PK
id_category (int4) PK
id_data (varchar 50) PK
id_ordinal (int4) PK (this field have a trigger to auto increment)
n_text (text)

When I run this function to do 900000 INSERT's it runs well and fast:

CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
   i integer;
BEGIN
       i := 1;

       while i <= 900000 loop
               insert into tdir_uris_text (uri, id_language, id_category, id_data, n_text) values ('http://localhos/teste' || i, 'PORT', '2', 'pagetitle', 'Pagina teste ' || i);

               i := i + 1;
       end loop;
   RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;

But when I do this with 100000 INSERT's it seems to never end the INSERT's operation, It is running at 5h now...

CREATE OR REPLACE FUNCTION doInserts() RETURNS integer AS
$BODY$
DECLARE
   i integer;
BEGIN
       i := 1;

       while i <= 100000 loop
               insert into tdir_uris_text (uri, id_language, id_catego开发者_运维技巧ry, id_data, n_text) values ('http://localhos/teste' || i, 'PORT', '2', 'pagetitle', 'Pagina teste ' || i);
               insert into tdir_uris_text (uri, id_language, id_category, id_data, n_text) values ('http://localhos/teste' || i, 'PORT', '2', 'country_ad', 'italy');
               insert into tdir_uris_text (uri, id_language, id_category, id_data, n_text) values ('http://localhos/teste' || i, 'PORT', '2', 'services_available', 'service 1');
               insert into tdir_uris_text (uri, id_language, id_category, id_data, n_text) values ('http://localhos/teste' || i, 'PORT', '2', 'services_available', 'service 2');

               i := i + 1;
       end loop;
   RETURN i;
END
$BODY$
LANGUAGE 'plpgsql' ;

What could be the problem here? Any clues? Could be the keys problem?

One more information. After I have inserted the 900000 registers I have used a "DELETE FROM" to delete the registers then I have run the Function that only inserts 100000 registers

Best Regards,

---

Use a sequence not a trigger. Everytime you test, make sure you truncate not delete.

---

(this field have a trigger to auto increment)

Why do you use a trigger? Can't you use a SERIAL (a.k.a. SEQUENCE) ?

And 100.000 insert's with the same value is a nice test, but has nothing to do with real live usage. These 100.000 inserts run within the same transaction, so you have to optimize the configuration settings for pretty large transactions. What are your settings for memory, WAL, etc. ?