Message-ID: <4DD07B4E.1020503@2ndquadrant.com>
Date: Sun, 15 May 2011 21:18:06 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: =?ISO-8859-1?Q?C=E9dric_Villemain?=
	<cedric.villemain.debian@gmail.com>, Josh Berkus <josh@agliodbs.com>,
	pgsql-performance@postgresql.org
Subject: Re: reducing random_page_cost from 4 to 2 to force index
 scan
References: <BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com>
	<4DB6F4BB020000250003CEB9@gw.wicourts.gov>
	<BANLkTindB68ogMg8zrR7GrYEV+mQQddj0A@mail.gmail.com>
	<BANLkTik6OaVkJGEjs5--vcynUE36mWbzbg@mail.gmail.com>
	<2823.1305314437@sss.pgh.pa.us>
	<BANLkTimTqXAy7_KqQz_OW1RvMbDd9ojfqg@mail.gmail.com>
	<4DCD90F5.2010501@agliodbs.com>
	<BANLkTikcVUeRfocJPe_W+jfi5k1+gcLnxg@mail.gmail.com>
	<4DD0195A.9040305@agliodbs.com>
	<BANLkTika=a95=Pm97Evi-Q7e_9vs4FnkCQ@mail.gmail.com>
	<4DD07855.8080907@postnewspapers.com.au>
In-Reply-To: <4DD07855.8080907@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:
> AFAIK, mincore() is only useful for mmap()ed files and for finding out
> if it's safe to access certain blocks of memory w/o risking triggering
> heavy swapping.
>
> It doesn't provide any visibility into the OS's block device / file
> system caches; you can't ask it "how much of this file is cached in RAM"
> or "is this range of blocks in this file cached in RAM".
>   

You should try out pgfincore if you think this can't be done!

> Even if you could, it's hard to see how an approach that relied on
> asking the OS through system calls about the cache state when planning
> every query could be fast enough to be viable.
>   

You can't do it in real-time.  You don't necessarily want that to even 
if it were possible; too many possibilities for nasty feedback loops 
where you always favor using some marginal index that happens to be in 
memory, and therefore never page in things that would be faster once 
they're read.  The only reasonable implementation that avoids completely 
unstable plans is to scan this data periodically and save some 
statistics on it--the way ANALYZE does--and then have that turn into a 
planner input.

The related secondary idea of just making assumptions about small 
tables/indexes, too, may be a useful heuristic to layer on top of this.  
There's a pile of ideas here that all seem reasonable both in terms of 
modeling real-world behavior and as things that could be inserted into 
the optimizer.  As usual, I suspect that work is needs to be followed by 
a giant testing exercise though.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books