Message-ID: <e23c5e2cd6df4bf425bb3a9801ecf671.squirrel@sq.gransy.com>
In-Reply-To: 
 <CA+AN_OYTUtY107A6gz6WV4ULDuMP1XHs5C6vhdYvnRjCXZzDOg@mail.gmail.com>
References: 
 <CA+AN_OYTUtY107A6gz6WV4ULDuMP1XHs5C6vhdYvnRjCXZzDOg@mail.gmail.com>
Date: Thu, 3 Nov 2011 17:18:00 +0100
Subject: Re: Optimization required for multiple insertions in
 PostgreSQL
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "siva palanisamy" <psivait@gmail.com>
Cc: pgsql-performance@postgresql.org,
 "Siva Palanisamy" <siva_p@hcl.com>
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

On 3 Listopad 2011, 16:52, siva palanisamy wrote:
> I basically have 3 tables. One being the core table and the other 2 depend
> on the 1st. I have the requirement to add upto 70000 records in the
> tables.
> I do have constraints (primary & foreign keys, index, unique etc) set for
> the tables. I can't go for bulk import (using COPY command) as there is no
> standard .csv file in requirement, and the mapping is explicitly required
> plus few validations are externally applied in a C based programming file.
> Each record details (upto 70000) will be passed from .pgc (an ECPG based C
> Programming file) to postgresql file. It takes less time for the 1st few
> records and the performance is turning bad to the latter records! The
> result is very sad that it takes days to cover upto 20000! What are the
> performance measures could I step in into this? Please guide me

As Kevin already pointed out, this overall and very vague description is
not sufficient. We need to know at least this for starters

- version of PostgreSQL
- environment (what OS, what hardware - CPU, RAM, drives)
- basic PostgreSQL config values (shared buffers, checkpoint segments)
- structure of the tables, indexes etc.
- output of vmstat/iostat collected when the inserts are slow

And BTW the fact that you're not using a standard .csv file does not mean
you can't use COPY. You can either transform the file to CSV or create it
on the fly.

Tomas