Re: Postgres insert performance and storage requirement compared to Oracle

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:
http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg146153.html
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...

--
Jesper