Message-ID: <4C59BCA8.2060603@2ndquadrant.com>
Date: Wed, 04 Aug 2010 15:16:56 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Hannu Krosing <hannu@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>
Subject: Re: Questions on query planner, join types, and work_mem
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>
In-Reply-To: <1280948634.3735.69.camel@hvost>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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, 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.  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