Slow Inserts, two different scenarios.

[Andre Lopes <lopes80andre@xxxxxxxxx>]

Hi,

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 in few time:

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_category, 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?


Best Regards,

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general