From: Greg Smith <greg@2ndquadrant.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Cédric Villemain <cedric.villemain.debian@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: Sun, 15 May 2011 21:18:06 -0400
Message-ID: <4DD07B4E.1020503@2ndquadrant.com> (raw)
In-Reply-To: <4DD07855.8080907@postnewspapers.com.au>
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>
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
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: greg@2ndquadrant.com, craig@postnewspapers.com.au, cedric.villemain.debian@gmail.com, josh@agliodbs.com
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
In-Reply-To: <4DD07B4E.1020503@2ndquadrant.com>
* 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