pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jesper Krogh <jesper@krogh.cc>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Divakar Singh <dpsmails@yahoo.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Thu, 28 Oct 2010 17:28:53 +0200
Message-ID: <4CC996B5.2050203@krogh.cc> (raw)
In-Reply-To: <AANLkTinBtTQ2mTPvA3n_yF=rLNfeK2RRW7FGOHqfWmv_@mail.gmail.com>
References: <402913.53404.qm@web65405.mail.ac4.yahoo.com>
	<AANLkTi=cy67frLbx+YH_uUk5vqPsxt8u-Q_cpU74pYMq@mail.gmail.com>
	<208728.42197.qm@web65409.mail.ac4.yahoo.com>
	<AANLkTikeYj52q1v1RcwCYR=9Kv5mxJmh+Q+KvXcmibYK@mail.gmail.com>
	<770715.39613.qm@web29003.mail.ird.yahoo.com>
	<AANLkTinaW3ZtGJbzgAARG0WfAOJxSPAbdVAZa6X_2dh+@mail.gmail.com>
	<4CC741FF.8050709@vmsinfo.com>
	<AANLkTinntqfT1b9xaRfGGqVwwsGhyA7=JhLsuQ10Xzyy@mail.gmail.com>
	<4CC74E31.6060307@vmsinfo.com>
	<AANLkTimf9Nj8A+Spiu9q3suvZ+h71CHXRFTXFo_5aBYj@mail.gmail.com>
	<AANLkTi=-fK6ODDNJPntq3OeUJQzKtHbsmkdDM851waOG@mail.gmail.com>
	<252538.42200.qm@web65405.mail.ac4.yahoo.com>
	<AANLkTik1Acnzpm62XgHADZtZ0sgkxSo=43jKrX7+N_f1@mail.gmail.com>
	<173298.80592.qm@web65406.mail.ac4.yahoo.com>
	<AANLkTin90tsRz4BNQP1gqrg6zCpQp0Yt0bQXKQHC22Ps@mail.gmail.com>
	<4CC8728B.9050205@krogh.cc>
	<AANLkTimMSMcqkAY9jMi8UZKveY3YS-p-V60KckvtDB6g@mail.gmail.com>
	<4CC881CB.8090806@krogh.cc>
	<AANLkTinBtTQ2mTPvA3n_yF=rLNfeK2RRW7FGOHqfWmv_@mail.gmail.com>

On 2010-10-28 15:13, Merlin Moncure wrote:
> On Wed, Oct 27, 2010 at 3:47 PM, Jesper Krogh<jesper@krogh.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@postgresql.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




view thread (53+ messages)

Message-ID: <4CC996B5.2050203@krogh.cc>
Permalink:  ../4CC996B5.2050203@krogh.cc/
Also on:    postgresql.org/message-id/4CC996B5.2050203@krogh.cc

 · 

reply
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: jesper@krogh.cc, mmoncure@gmail.com, dpsmails@yahoo.com, robertmhaas@gmail.com, mladen.gogala@vmsinfo.com
  Subject: Re: Postgres insert performance and storage requirement compared to Oracle
  In-Reply-To: <4CC996B5.2050203@krogh.cc>

* 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