, October 27, 2010 4:46:53 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas wrote:
> On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
> wrote:
>> The table is created with "on commit oblite
On 2010-10-28 15:13, Merlin Moncure wrote:
On Wed, Oct 27, 2010 at 3:47 PM, Jesper Krogh wrote:
On 2010-10-27 20:51, Merlin Moncure wrote:
Yes, I am quite aware of how the o/s page cache works. All else being
equal, I more compact database obviously would be preferred. However
'al
On Wed, Oct 27, 2010 at 3:47 PM, Jesper Krogh wrote:
> On 2010-10-27 20:51, Merlin Moncure wrote:
>
>>> Yes, I am quite aware of how the o/s page cache works. All else being
>>> equal, I more compact database obviously would be preferred. However
>>> 'all else' is not necessarily equal. I can m
t;
> Best Regards,
> Divakar
>
>
> From: Alex Hunsaker
> To: Divakar Singh
> Cc: Steve Singer ; j...@commandprompt.com;
> pgsql-performance@postgresql.org
> Sent: Thu, October 28, 2010 1:15:06 AM
> Subject: Re: [PERFORM] Postgres insert performan
On Wed, Oct 27, 2010 at 21:08, Divakar Singh wrote:
> So another question pops up: What method in PostgreSQL does the stored proc
> use when I issue multiple insert (for loop for 100 thousand records) in the
> stored proc?
It uses prepared statements (unless you are using execute). There is
also
tement? or something else?
Best Regards,
Divakar
From: Alex Hunsaker
To: Divakar Singh
Cc: Steve Singer ; j...@commandprompt.com;
pgsql-performance@postgresql.org
Sent: Thu, October 28, 2010 1:15:06 AM
Subject: Re: [PERFORM] Postgres insert performance a
On 2010-10-27 20:51, Merlin Moncure wrote:
Yes, I am quite aware of how the o/s page cache works. All else being
equal, I more compact database obviously would be preferred. However
'all else' is not necessarily equal. I can mount my database on bzip
volume, that must make it faster, right?
On Wed, Oct 27, 2010 at 08:00, Divakar Singh wrote:
> I am attaching my code below.
> Is any optimization possible in this?
> Do prepared statements help in cutting down the insert time to half for this
> kind of inserts?
In half? not for me. Optimization possible? Sure, using the code you
paste
On Wed, Oct 27, 2010 at 2:42 PM, Jesper Krogh wrote:
> On 2010-10-27 20:28, Merlin Moncure wrote:
>>
>> Postgres indexes are pretty compact, and oracle (internals I am not
>> familiar with) also has to do MVCC type management, so I am suspecting
>> your measurement is off (aka, operator error) or
l compared to Oracle.
*From:* Merlin Moncure
*To:* Divakar Singh
*Cc:* Robert Haas ; Mladen Gogala
; pgsql-performance@postgresql.org
*Sent:* Wed, October 27, 2010 11:36:00 PM
*Subject:* Re: [PERFORM] Postgres insert performance and storage
requirement compared to Oracle
On Tue, Oct
On 2010-10-27 20:28, Merlin Moncure wrote:
Postgres indexes are pretty compact, and oracle (internals I am not
familiar with) also has to do MVCC type management, so I am suspecting
your measurement is off (aka, operator error) or oracle is cheating
somehow by optimizing away storage requirements
On Wed, Oct 27, 2010 at 2:14 PM, Divakar Singh wrote:
> yes this is a very clearly visible problem.
> The difference b/w oracle and PG increases with more rows.
> when oracle takes 3 GB, PG takes around 6 GB.
> I only use varchar.
> I will try to use your tips on "smart table layout, toast compres
t have any performance penalty?
Best Regards,
Divakar
From: Merlin Moncure
To: Divakar Singh
Cc: Robert Haas ; Mladen Gogala
; pgsql-performance@postgresql.org
Sent: Wed, October 27, 2010 11:36:00 PM
Subject: Re: [PERFORM] Postgres insert performance and storage r
On Wed, Oct 27, 2010 at 2:06 PM, Mladen Gogala
wrote:
> Scott, I find this very hard to believe. If you are inserting into a
> temporary table and then into the target table, you will do 2 inserts
> instead of just one. What you are telling me is that it is faster for me to
> drive from NYC to Was
On 10/27/2010 1:48 PM, Scott Carey wrote:
It is almost always significantly faster than a direct bulk load into a table.
* The temp table has no indexes, the final table usually does, bulk operations
on indexes are faster than per row operations.
* The final table might require both updates and
On Tue, Oct 26, 2010 at 11:10 PM, Divakar Singh wrote:
> Dear All,
> Thanks for your inputs on the insert performance part.
> Any suggestion on storage requirement?
> VACUUM is certainly not an option, because this is something related to
> maintenance AFTER insertion.
> I am talking about the pla
On Oct 26, 2010, at 2:54 PM, Mladen Gogala wrote:
> On 10/26/2010 5:27 PM, Jon Nelson wrote:
>> start loop:
>> populate rows in temporary table
>> insert from temporary table into permanent table
>> truncate temporary table
>> loop
>>
>> I do something similar, where I COPY data to a tem
rg
Sent: Tue, October 26, 2010 12:22:31 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
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
On Tue, Oct 26, 2010 at 6:50 PM, Robert Haas wrote:
> On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
> wrote:
>> The table is created with "on commit obliterate rows" option which means
>> that there is no need to do "truncate". The "truncate" command is a heavy
>> artillery. Truncating a tempora
On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
wrote:
> The table is created with "on commit obliterate rows" option which means
> that there is no need to do "truncate". The "truncate" command is a heavy
> artillery. Truncating a temporary table is like shooting ducks in a duck
> pond, with a how
On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala
wrote:
> On 10/26/2010 5:27 PM, Jon Nelson wrote:
>>
>> start loop:
>> populate rows in temporary table
>> insert from temporary table into permanent table
>> truncate temporary table
>> loop
>>
>> I do something similar, where I COPY data to
On 10/26/2010 5:27 PM, Jon Nelson wrote:
start loop:
populate rows in temporary table
insert from temporary table into permanent table
truncate temporary table
loop
I do something similar, where I COPY data to a temporary table, do
lots of manipulations, and then perform a series of
On Tue, Oct 26, 2010 at 4:02 PM, Mladen Gogala
wrote:
> On 10/26/2010 11:41 AM, Merlin Moncure wrote:
>>
>> yup, that's exactly what I mean -- this will give you more uniform
>> insert performance (your temp table doesn't even need indexes). Every
>> N records (say 1) you send to permanent an
On 10/26/2010 11:41 AM, Merlin Moncure wrote:
yup, that's exactly what I mean -- this will give you more uniform
insert performance (your temp table doesn't even need indexes). Every
N records (say 1) you send to permanent and truncate the temp
table. Obviously, this is more fragile approac
On Tue, Oct 26, 2010 at 11:08 AM, Leonardo Francalanci wrote:
>> temp tables are not wal logged or
>> synced. Periodically they can be flushed to a permanent table.
>
>
> What do you mean with "Periodically they can be flushed to
> a permanent table"? Just doing
>
> insert into tabb select * f
> temp tables are not wal logged or
> synced. Periodically they can be flushed to a permanent table.
What do you mean with "Periodically they can be flushed to
a permanent table"? Just doing
insert into tabb select * from temptable
or using a proper, per-temporary table command???
--
On Tue, Oct 26, 2010 at 7:44 AM, Divakar Singh wrote:
> Hi Merlin,
> Thanks for your quick input.
> Well 1 difference worth mentioning:
> I am inserting each row in a separate transaction, due to design of my
> program.
Well, that right there is going to define your application
performance. You h
, October 26, 2010 2:21:02 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure wrote:
> I ran the following tests w/libpqtypes. While you probably wont end
> up using libpqtypes, it's illustrative
On Mon, Oct 25, 2010 at 4:28 PM, Merlin Moncure wrote:
> I ran the following tests w/libpqtypes. While you probably wont end
> up using libpqtypes, it's illustrative to mention it because it's
> generally the easiest way to get data into postgres and by far the
> fastest (excluding 'COPY'). sour
On Mon, Oct 25, 2010 at 2:12 PM, Divakar Singh wrote:
> Hello Experts,
> My application uses Oracle DB, and makes use of OCI interface.
> I have been able to develop similar interface using postgreSQL library.
> However, I have done some tests but results for PostgreSQL have not been
> encouraging
On October 25, 2010 11:36:24 am Divakar Singh wrote:
> Above results show good INSERT performance of PG when using SQL procedures.
> But performance when I use C++ lib is very bad. I did that test some time
> back so I do not have data for that right now.
Wrap it in a transaction.
--
Sent via pg
On Mon, Oct 25, 2010 at 12:36 PM, Divakar Singh wrote:
>
> Storage test was simple, but the data (seconds taken) for INSERT test for PG
> vs Oracle for 1, 2, 3,4 and 5 indexes was:
> PG:
> 25
> 30
> 37
> 42
> 45
>
> Oracle:
>
> 33
> 43
> 50
> 65
> 68
> Rows inserted: 100,000
> Above results show
__
> From: Ray Stell
> To: Divakar Singh
> Sent: Tue, October 26, 2010 12:05:23 AM
> Subject: Re: [PERFORM] Postgres insert performance and storage requirement
> compared to Oracle
>
> On Mon, Oct 25, 2010 at 11:12:40AM -0700, Divakar Singh wrote:
> >
> >
so I do not have data for that right now.
*From:* Scott Marlowe
*To:* Divakar Singh
*Cc:* pgsql-performance@postgresql.org
*Sent:* Mon, October 25, 2010 11:56:27 PM
*Subject:* Re: [PERFORM] Postgres insert performance and storage
requirement c
hich version of postgresql your using.
[Latest, 9.x]
Best Regards,
Divakar
From: Steve Singer
To: Divakar Singh
Cc: j...@commandprompt.com; pgsql-performance@postgresql.org
Sent: Tue, October 26, 2010 12:16:46 AM
Subject: Re: [PERFORM] Postgres insert
Marlowe ; pgsql-performance@postgresql.org
Sent: Tue, October 26, 2010 12:08:52 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
On Mon, 2010-10-25 at 11:36 -0700, Divakar Singh wrote:
>
> 68 Rows inserted: 100,000
> Above results show go
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 no
On Mon, 2010-10-25 at 11:36 -0700, Divakar Singh wrote:
>
> 68 Rows inserted: 100,000
> Above results show good INSERT performance of PG when using SQL
> procedures. But
> performance when I use C++ lib is very bad. I did that test some time
> back so I
> do not have data for that right now.
performance and storage requirement
compared to Oracle
On Mon, Oct 25, 2010 at 12:12 PM, Divakar Singh wrote:
> Hello Experts,
> My application uses Oracle DB, and makes use of OCI interface.
> I have been able to develop similar interface using postgreSQL library.
> However, I have done so
On Mon, Oct 25, 2010 at 2:12 PM, Divakar Singh wrote:
> 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 dec
> 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 hav
On Mon, Oct 25, 2010 at 12:12 PM, Divakar Singh wrote:
> Hello Experts,
> My application uses Oracle DB, and makes use of OCI interface.
> I have been able to develop similar interface using postgreSQL library.
> However, I have done some tests but results for PostgreSQL have not been
> encouragin
On Mon, 2010-10-25 at 11:12 -0700, 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
Hello Experts,
My application uses Oracle DB, and makes use of OCI interface.
I have been able to develop similar interface using postgreSQL library.
However, I have done some tests but results for PostgreSQL have not been
encouraging for a few of them.
My questions/scenarios are:
1. How does Po
44 matches
Mail list logo
