Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 3068D1337B81 for ; Mon, 25 Oct 2010 15:52:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 96087-05-5 for ; Mon, 25 Oct 2010 18:52:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm2.bullet.mail.ac4.yahoo.com (nm2.bullet.mail.ac4.yahoo.com [98.139.52.199]) by mail.postgresql.org (Postfix) with SMTP id E3A511337C06 for ; Mon, 25 Oct 2010 15:52:32 -0300 (ADT) Received: from [98.139.52.189] by nm2.bullet.mail.ac4.yahoo.com with NNFMP; 25 Oct 2010 18:52:31 -0000 Received: from [98.139.52.131] by tm2.bullet.mail.ac4.yahoo.com with NNFMP; 25 Oct 2010 18:52:31 -0000 Received: from [127.0.0.1] by omp1014.mail.ac4.yahoo.com with NNFMP; 25 Oct 2010 18:52:31 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 644600.32242.bm@omp1014.mail.ac4.yahoo.com Received: (qmail 25923 invoked by uid 60001); 25 Oct 2010 18:52:31 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1288032751; bh=Lv7vgBvpXbuVa8VgfIlxKoyLWkHsVnCQ3VctjqvVgEw=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=g5wgRh1WAgxQseK3gRGk85Yd310M207IX5jVwyPbl3FpDm3pStzCrhedkb9A5EyoR97kW7FKbO2oKFbj30cNZ61Nv5s7v65wSNtv+KF3w0h4yFQOZY+on8cuOy4wEOqiwVGXHx/nqRktNoMGJlx6GnRyppAnuks7aAisrCk3TmU= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=1OtFmxHu70JPOOx2Ye4mPcVbqGcqLGAMeKFmUD5cUMDM4AVM3kwp1ijwzTIUCySBOEF0+BKsIzyNnEuemPbW37XwqkFYswrNP2itumFBYHiz1PKbrcPwg3+MBa/QVWiKLENFsIzMoKaQxsGcuaBh2N3RiOQZvgm0Q2V26m+jiBU=; Message-ID: <441423.23877.qm@web65406.mail.ac4.yahoo.com> X-YMail-OSG: e3IdY84VM1kEwkR5xmg1As8wQ106tdvRAmtg6ne0MjKczsW HvnUVQ.9_YC_MFg0EyOUsJr.GHXd2_Kt32xZstUEM5lT2Qx9HY2ZCYHsn5g. lHiUAkadboEKnHyjhWZwaehqH1aPUy.EBxqpFzY3Fz3Bjjq7Tw8HOFqk8_nn xmx1LxZOUrPIcJ3ffmCFUfVW_i.UIdhuMCmPJ8UeXaUmVzLzOzB8mT_VKnrC X.pAhUdBWMJxhKFt2ST5UWgE7x17wv6HoW2ID9rUHhGV092P0p.nguE..wu3 TcIHtzhj.3V.9pBY5nobR_nunWGAlO2Z8H7ecAerhtoaH5bYp0heQJrB3xRG y3sZRQpE- Received: from [194.237.142.6] by web65406.mail.ac4.yahoo.com via HTTP; Mon, 25 Oct 2010 11:52:31 PDT X-Mailer: YahooMailRC/504.5 YahooMailWebService/0.8.107.284920 References: <402913.53404.qm@web65405.mail.ac4.yahoo.com> <1288030833.8930.30.camel@jd-desktop> <772167.16310.qm@web65409.mail.ac4.yahoo.com> <4CC5D096.5030406@ca.afilias.info> Date: Mon, 25 Oct 2010 11:52:31 -0700 (PDT) From: Divakar Singh Subject: Re: Postgres insert performance and storage requirement compared to Oracle To: Steve Singer Cc: jd@commandprompt.com, pgsql-performance@postgresql.org In-Reply-To: <4CC5D096.5030406@ca.afilias.info> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-900279067-1288032751=:23877" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/407 X-Sequence-Number: 40825 --0-900279067-1288032751=:23877 Content-Type: text/plain; charset=us-ascii Answers: How are you using libpq? -Are you opening and closing the database connection between each insert? [Need to check, will come back on this] -Are you doing all of your inserts as one big transaction or are you doing a transaction per insert [Answer: for C++ program, one insert per transaction in PG as well as Oracle. But in stored proc, I think both use only 1 transaction for all inserts] -Are you using prepared statements for your inserts? [Need to check, will come back on this] -Are you using the COPY command to load your data or the INSERT command? [No] -Are you running your libpq program on the same server as postgresql? [Yes] -How is your libpq program connecting to postgresql, is it using ssl? [No] If your run "VACUUM VERBOSE tablename" on the table, what does it say? [Need to check, will come back on this] You also don't mention which version of postgresql your using. [Latest, 9.x] Best Regards, Divakar ________________________________ From: Steve Singer To: Divakar Singh Cc: jd@commandprompt.com; pgsql-performance@postgresql.org Sent: Tue, October 26, 2010 12:16:46 AM Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle 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 > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --0-900279067-1288032751=:23877 Content-Type: text/html; charset=us-ascii

Answers:

How are you using libpq?
-Are you opening and closing the database connection between each insert?

[Need to check, will come back on this]

-Are you doing all of your inserts as one big transaction or are you  doing a transaction per insert

[Answer: for C++ program, one insert per transaction in PG as well as Oracle. But in stored proc, I think both use only 1 transaction for all inserts]

-Are you using prepared statements for your inserts?

[Need to check, will come back on this]

-Are you using the COPY command to load your data or the INSERT command?

[No]

-Are you running your libpq program on the same server as postgresql?

[Yes]

-How is your libpq program connecting to postgresql, is it using ssl?

[No]

If your run "VACUUM VERBOSE tablename" on the table, what does it say?

[Need to check, will come back on this]

You also don't mention which version of postgresql your using.

[Latest, 9.x]
 
Best Regards,
Divakar


From: Steve Singer <ssinger@ca.afilias.info>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: jd@commandprompt.com; pgsql-performance@postgresql.org
Sent: Tue, October 26, 2010 12:16:46 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

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
>
>


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--0-900279067-1288032751=:23877--