From: Steve Singer <ssinger@ca.afilias.info>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: jd@commandprompt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Mon, 25 Oct 2010 14:46:46 -0400
Message-ID: <4CC5D096.5030406@ca.afilias.info> (raw)
In-Reply-To: <772167.16310.qm@web65409.mail.ac4.yahoo.com>
References: <402913.53404.qm@web65405.mail.ac4.yahoo.com>
<1288030833.8930.30.camel@jd-desktop>
<772167.16310.qm@web65409.mail.ac4.yahoo.com>
On 10-10-25 02:31 PM, Divakar Singh wrote:
>
> > My questions/scenarios are:
> >
> > 1. How does PostgreSQL perform when inserting data into an indexed
> > (type: btree)
> > table? Is it true that as you add the indexes on a table, the
> > performance
> > deteriorates significantly whereas Oracle does not show that much
> > performance
> > decrease. I have tried almost all postgreSQL performance tips
> > available. I want
> > to have very good "insert" performance (with indexes), "select"
> > performance is
> > not that important at this point of time.
>
> -- Did you test?
>
> Yes. the performance was comparable when using SQL procedure. However,
> When I used libpq, PostgreSQL performed very bad. There was some
> difference in environment also between these 2 tests, but I am assuming
> libpq vs SQL was the real cause. Or it was something else?
So your saying that when you load the data with psql it loads fine, but
when you load it using libpq it takes much longer?
How are you using libpq?
-Are you opening and closing the database connection between each insert?
-Are you doing all of your inserts as one big transaction or are you
doing a transaction per insert
-Are you using prepared statements for your inserts?
-Are you using the COPY command to load your data or the INSERT command?
-Are you running your libpq program on the same server as postgresql?
-How is your libpq program connecting to postgresql, is it using ssl?
>
> Some 10-12 columns ( like 2 timestamp, 4 int and 4 varchar), with 5
> indexes on varchar and int fields including 1 implicit index coz of PK.
If your run "VACUUM VERBOSE tablename" on the table, what does it say?
You also don't mention which version of postgresql your using.
>
>
> Joshua D. Drake
>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
>
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: ssinger@ca.afilias.info, dpsmails@yahoo.com, jd@commandprompt.com
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
In-Reply-To: <4CC5D096.5030406@ca.afilias.info>
* 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