Insert performance with composite index

From: Divakar Singh <dpsmails(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Insert performance with composite index
Date: 2010-11-01 12:49:14
Message-ID: 293254.80548.qm@web65413.mail.ac4.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marti Raudsepp 2010-11-01 12:53:17 Re: Insert performance with composite index
Previous Message Mark Kirkwood 2010-11-01 05:03:33 Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?