From: Tomas Vondra <tv@fuzzy.cz>
To: siva palanisamy <psivait@gmail.com>
Cc: pgsql-performance@postgresql.org, "Siva Palanisamy" <siva_p@hcl.com>
Subject: Re: Optimization required for multiple insertions in PostgreSQL
Date: Thu, 3 Nov 2011 17:18:00 +0100
Message-ID: <e23c5e2cd6df4bf425bb3a9801ecf671.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CA+AN_OYTUtY107A6gz6WV4ULDuMP1XHs5C6vhdYvnRjCXZzDOg@mail.gmail.com>
References: <CA+AN_OYTUtY107A6gz6WV4ULDuMP1XHs5C6vhdYvnRjCXZzDOg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, psivait@gmail.com, siva_p@hcl.com
Subject: Re: Optimization required for multiple insertions in PostgreSQL
In-Reply-To: <e23c5e2cd6df4bf425bb3a9801ecf671.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox