Subject: Re: Questions on query planner, join types, and work_mem
From: Hannu Krosing <hannu@krosing.net>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
	Peter Hussey <peter@labkey.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <4C59BCA8.2060603@2ndquadrant.com>
References: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
	<18551.1280275502@sss.pgh.pa.us>
	<AANLkTimXMR0jMdw+66E4Xx=SqNV37Ne9WfkFjPFZVjbV@mail.gmail.com>
	<25534.1280498632@sss.pgh.pa.us>
	<AANLkTik3oY1=F_uC843c84LxBLNdHxZaWdfjxrAtyBeu@mail.gmail.com>
	<1280819031.14579.38.camel@hvost>
	<AANLkTik6UBt2ezVFzdCM8iaUPkra_7f-Hs_AmGZgvFhh@mail.gmail.com>
	<1280944268.3735.12.camel@hvost>  <22961.1280944836@sss.pgh.pa.us>
	<1280947287.3735.56.camel@hvost> <1280948634.3735.69.camel@hvost>
	<4C59BCA8.2060603@2ndquadrant.com>
Content-Type: text/plain; charset="UTF-8"
Date: Wed, 04 Aug 2010 22:51:44 +0300
Message-ID: <1280951504.3735.93.camel@hvost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2010-08-04 at 15:16 -0400, Greg Smith wrote:
> Hannu Krosing wrote:
> > There was ample space for keeping the indexes in linux cache (it has 1GB
> > cached currently) though the system may have decided to start writing it
> > to disk, so I suspect that most of the time was spent copying random
> > index pages back and forth between shared buffers and disk cache.
> >   
> 
> Low shared_buffers settings will result in the same pages more often 
> being written multiple times per checkpoint,

Do you mean "written to disk", or written out from shared_buffers to
disk cache ?

>  particularly index pages, 
> which is less efficient than keeping in the database cache and updating 
> them there.  This is a slightly different issue than just the overhead 
> of copying them back and forth; by keeping them in cache, you actually 
> reduce writes to the OS cache. 

That's what I meant. Both writes to and read from the OS cache take a
significant amount of time once you are not doing real disk I/O.

> What I do to quantify that is...well, 
> the attached shows it better than I can describe; only works on 9.0 or 
> later as it depends on a feature I added for this purpose there.  It 
> measures exactly how much buffer cache churn happened during a test, in 
> this case creating a pgbench database.
> 
> -- 
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com   www.2ndQuadrant.us
> 
>