How to optimize insert statements ?

Hello,

I'm encountering a performance issue with insert statements.
I push to Postgres an xml file with 2460 objects representing 2460
insert statements in a single transaction commited when the xml file is
totally read.
I don't know how to solve the issue, how to tune/optimize Postgres or my
statements. I limited the number of index and commented lots of lines in
the PERL trigger attached to my table without any success. Any
hints/advises are welcome.

Thanks in advance,
Christian


I have activated the execution time logging. The first insert statements
are very fast, but with time and objects inserted, every insert
statement becomes slower:
2007-07-24 10:30:39 LOG: duration: 0.000 ms statement: BEGIN; SET
TRANSACTION ISOLATION LEVEL READ COMMITTED;
[...]
2007-07-24 10:30:58 LOG: duration: 0.000 ms statement: INSERT INTO
production.product_downloads ( deploytool_id, name, fk_product,
fk_product_version, patch_level, category, description, size,
internal_id, deployment_date, release_date, download_update_date )
VALUES ( 'FILE_2410', 'jrules50_4.exe', 12, 84, 4, 'Patch', 'JRules 5.0
update 4 - build 44', 314525023, 'patch/jrules/1147/jrules50_4.exe',
'2005-06-09 09:31:52.000000', '2005-06-07 00:00:00.000000', NULL );
SELECT next_id FROM core.tables WHERE name =
'production.product_downloads'
[...]
2007-07-24 10:32:33 LOG: duration: 47.000 ms statement: INSERT INTO
production.product_downloads ( deploytool_id, name, fk_product,
fk_product_version, patch_level, category, description, size,
internal_id, deployment_date, release_date, download_update_date )
VALUES ( 'FILE_528', 'gadgets_views40_89.rs6000.tar.gz', 30, 273, 89,
'Patch', NULL, 1224154,
'patch/views/150/gadgets_views40_89.rs6000.tar.gz', '2001-03-19
00:00:00.000000', '2001-03-19 00:00:00.000000', '2001-03-19
00:00:00.000000' ); SELECT next_id FROM core.tables WHERE name =
'production.product_downloads'
[...]
2007-07-24 10:35:31 LOG: duration: 78.000 ms statement: INSERT INTO
production.product_downloads ( deploytool_id, name, fk_product,
fk_product_version, patch_level, category, description, size,
internal_id, deployment_date, release_date, download_update_date )
VALUES ( 'FILE_1930', 'web_views50_254.hp32_11_3.30.tar.gz', 30, 261,
254, 'Patch', NULL, 562328,
'patch/views/14940/web_views50_254.hp32_11_3.30.tar.gz', '2003-10-03
18:50:37.000000', '2003-10-03 00:00:00.000000', NULL ); SELECT next_id
FROM core.tables WHERE name = 'production.product_downloads'
[...]
2007-07-24 10:37:50 LOG: duration: 93.999 ms statement: INSERT INTO
production.product_downloads ( deploytool_id, name, fk_product,
fk_product_version, patch_level, category, description, size,
internal_id, deployment_date, release_date, download_update_date )
VALUES ( 'FILE_1942', 'foundation_views402_196.alpha_4_6.1.tar.gz', 30,
260, 196, 'Patch', NULL, 10765925,
'patch/views/18824/foundation_views402_196.alpha_4_6.1.tar.gz',
'2004-03-12 10:56:52.000000', '2004-03-12 00:00:00.000000', NULL );
SELECT next_id FROM core.tables WHERE name =
'production.product_downloads'
[...]
2007-07-24 10:53:08 LOG: duration: 171.999 ms statement: INSERT INTO
production.product_downloads ( deploytool_id, name, fk_product,
fk_product_version, patch_level, category, description, size,
internal_id, deployment_date, release_date, download_update_date )
VALUES ( 'FILE_7099', 'manager_views501_293.hp64_11_3.15.tar.gz', 30,
275, 293, 'Patch', NULL, 731466,
'patch/views/50549/manager_views501_293.hp64_11_3.15.tar.gz',
'2007-06-29 14:36:16.000000', '2007-06-27 00:00:00.000000', NULL );
SELECT next_id FROM core.tables WHERE name =
'production.product_downloads'
[...]
2007-07-24 10:53:15 LOG: duration: 233.999 ms statement: COMMIT

Here is the table schema, the index and the trigger code written in
PERL:
CREATE TABLE production.product_downloads
(
nid integer NOT NULL,
deploytool_id character varying(64),
name character varying(128),
fk_product integer NOT NULL REFERENCES production.products(nid) ON
DELETE RESTRICT,
fk_product_version integer NOT NULL REFERENCES
production.product_versions(nid) ON DELETE RESTRICT,
patch_level integer,
category character varying(32),
description text,
size integer,
internal_id character varying(128),
deployment_date timestamp with time zone,
release_date date,
download_update_date timestamp with time zone,

CONSTRAINT product_downloads_pkey PRIMARY KEY (nid),
CONSTRAINT product_downloads_deploytool_id_key UNIQUE (deploytool_id)
)
WITHOUT OIDS;

CREATE INDEX product_downloads_deploytool_id_idx ON
production.product_downloads(deploytool_id);

CREATE TRIGGER trigger_product_downloads BEFORE INSERT OR DELETE OR
UPDATE
ON production.product_downloads FOR EACH ROW EXECUTE PROCEDURE
core.historize_and_notify('production');

CREATE OR REPLACE FUNCTION core.historize_and_notify() RETURNS "trigger"
AS $BODY$
my $schemaName = @{$_TD->{args}}[0];
if ($_TD->{event} eq "DELETE") {
# All the code is commented here
return;
}
elsif ($_TD->{event} eq "INSERT") {
if (!defined($_TD->{new}{row_id})) {
my $retrieved = spi_exec_query("SELECT next_id, prefix FROM
core.tables WHERE name = '".$schemaName.".".$_TD->{relname}."'");
$_TD->{new}{nid} = $retrieved->{rows}[0]->{next_id};
my $incremented = spi_exec_query("UPDATE core.tables SET
next_id=".($_TD->{new}{nid}+1)."WHERE name =
'".$schemaName.".".$_TD->{relname}."'");
}
# All the code is commented from here
return "MODIFY";
} elsif ($_TD->{event}eq "UPDATE") {
# All the code is commented from here
return "MODIFY";
}
return;
$BODY$ LANGUAGE 'plperl' VOLATILE;

CREATE TABLE core.tables
(
name character(64) NOT NULL,
prefix character(4) NOT NULL,
description text,
next_id integer,

CONSTRAINT ods_pkey PRIMARY KEY (name),
CONSTRAINT ods_id_prefix_key UNIQUE (prefix)
)
WITHOUT OIDS;

CREATE INDEX tables_name_idx
ON core.tables USING btree (name);