Skip site navigation (1) Skip section navigation (2)

Re: Postgres insert performance and storage requirement compared to Oracle

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Divakar Singh <dpsmails(at)yahoo(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: 2010-10-28 15:28:53
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-performance
On 2010-10-28 15:13, Merlin Moncure wrote:
> On Wed, Oct 27, 2010 at 3:47 PM, Jesper Krogh<jesper(at)krogh(dot)cc>  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 mount my database on bzip
>>>> volume, that must make it faster, right?  wrong.  I understand the
>>>> postgres storage architecture pretty well, and the low hanging fruit
>>>> having been grabbed further layout compression is only going to come
>>>> as a result of tradeoffs.
>> Or configureabillity.. Not directly related to overall space consumption
>> but I have been working on a patch that would make TOAST* kick in
>> earlier in the process, giving a "slimmer" main table with visibillity
>> information
>> and simple columns and moving larger colums more aggressively to TOAST.
> Do you have any benchmarks supporting if/when such a change would be beneficial?
On, IO-bound queries it pretty much translates to the ration between
the toast-table-size vs. the main-table-size.

Trying to aggressively speed up "select count(*) from table" gives this:
with shutdown of pg and drop caches inbetween... the "default" select 
count (*) on 50K tuples
gives 4.613ms (2 tuples pr page) vs. 318ms... (8 tuples pr page).

PG default is inbetween...


In response to

pgsql-performance by date

Next:From: C├ędric VillemainDate: 2010-10-28 15:31:29
Subject: Re: How does PG know if data is in memory?
Previous:From: Mladen GogalaDate: 2010-10-28 15:23:13
Subject: Re: Slow Query- Simple taking

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-10-28 15:39:11
Subject: Re: plperl arginfo
Previous:From: Andrew DunstanDate: 2010-10-28 15:24:50
Subject: plperl arginfo

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group