DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type;
	b=vfs7uGqIP3T0rc6WI01NP3+BZ6og8sAR9aN4sf4FP9q2VOqyUzBA3EqXat8ZXb19+vn01HRi5HTwZGQEi4D0oRVbuqC/cGGVAXR5z5EtuaDHQsZnZCSn5sanF/enAQY8qol5P9OSW684p3rpdtEm9e2V1LaMBi56vXOJDiRcW7Q=;
Message-ID: <293254.80548.qm@web65413.mail.ac4.yahoo.com>
Date: Mon, 1 Nov 2010 05:49:14 -0700 (PDT)
From: Divakar Singh <dpsmails@yahoo.com>
Subject: Insert performance with composite index
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-189303600-1288615754=:80548"

--0-189303600-1288615754=:80548
Content-Type: text/plain; charset=us-ascii

Hi,
I am trying to tune my libpq program for insert performance.
When I tried inserting 1M rows into a table with a Primary Key, it took almost 
62 seconds.
After adding a composite index of 2 columns, the performance degrades to 125 
seconds.
I am using COPY to insert all data in 1 transaction.

the table definition is 

CREATE TABLE ABC
(
  event integer,
  innodeid character varying(80),
  innodename character varying(80),
  sourceid character varying(300),
  intime timestamp(3) without time zone,
  outnodeid character varying(80),
  outnodename character varying(80),
  destinationid character varying(300),
  outtime timestamp(3) without time zone,
  bytes integer,
  cdrs integer,
  tableindex integer NOT NULL,
  noofsubfilesinfile integer,
  recordsequenceintegerlist character varying(1000),
  CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
)

the index definition is 


CREATE INDEX "PK_AT2"
  ON ABC
  USING btree
  (event, tableindex)
TABLESPACE sample;

Any tip to increase the insert performance in this case?

It would also be helpful if someone can send comprehensive libpq programming 
guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me.


 Best Regards,
Divakar


--0-189303600-1288615754=:80548
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:10pt;color:#000000;">Hi,<br>I am trying to tune my libpq program for insert performance.<br>When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds.<br>After adding a composite index of 2 columns, the performance degrades to 125 seconds.<br>I am using COPY to insert all data in 1 transaction.<br><br>the table definition is <br><br>CREATE TABLE ABC<br>(<br>&nbsp; event integer,<br>&nbsp; innodeid character varying(80),<br>&nbsp; innodename character varying(80),<br>&nbsp; sourceid character varying(300),<br>&nbsp; intime timestamp(3) without time zone,<br>&nbsp; outnodeid character varying(80),<br>&nbsp; outnodename character varying(80),<br>&nbsp; destinationid character varying(300),<br>&nbsp; outtime timestamp(3) without time zone,<br>&nbsp; bytes integer,<br>&nbsp; cdrs
 integer,<br>&nbsp; tableindex integer NOT NULL,<br>&nbsp; noofsubfilesinfile integer,<br>&nbsp; recordsequenceintegerlist character varying(1000),<br>&nbsp; CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)<br>)<br><br>the index definition is <br><br><br>CREATE INDEX "PK_AT2"<br>&nbsp; ON ABC<br>&nbsp; USING btree<br>&nbsp; (event, tableindex)<br>TABLESPACE sample;<br><br>Any tip to increase the insert performance in this case?<br><br>It would also be helpful if someone can send comprehensive libpq programming guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me.<br><br><div>&nbsp;</div>Best Regards,<br>Divakar<div><br></div>
</div><br>

      </body></html>
--0-189303600-1288615754=:80548--