How to optimize insert statements ?

From: Christian Leclerc
Subject: How to optimize insert statements ?
Date: ,
Msg-id: 1F31510056BF344C8D1076017C1367FA0FCE7B@parmbx02.ilog.biz
(view: Whole thread, Raw)
Responses: Re: How to optimize insert statements ?  (Tom Lane)
List: pgsql-novice

Tree view

How to optimize insert statements ?  ("Christian Leclerc", )
 Re: How to optimize insert statements ?  (Tom Lane, )
  Re: How to optimize insert statements ?  ("Christian Leclerc", )
   Re: How to optimize insert statements ?  (Sean Davis, )
    Re: How to optimize insert statements ?  ("Christian Leclerc", )

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
 
 
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);

pgsql-novice by date:

From: Tom Lane
Date:
Subject: Re: How to optimize insert statements ?
From: "Claude Chaloux"
Date:
Subject: DEFERABLE vs. NOT DEFERABLE constraints