Message-ID: <4DD0AB16.5060808@krogh.cc>
Date: Mon, 16 May 2011 06:41:58 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Craig Ringer <craig@postnewspapers.com.au>, =?ISO-8859-1?Q?C=E9dric_?=
	=?ISO-8859-1?Q?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>
	<4DD07B4E.1020503@2ndquadrant.com>
In-Reply-To: <4DD07B4E.1020503@2ndquadrant.com>
Content-Type: multipart/alternative;
	boundary="------------080203040905070308090406"

This is a multi-part message in MIME format.
--------------080203040905070308090406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2011-05-16 03:18, Greg Smith wrote:
>  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.

Would that be feasible? Have process collecting the data every now-and-then
probably picking some conservative-average function and feeding
it into pg_stats for each index/relation?

To me it seems like a robust and fairly trivial way to to get better 
numbers. The
fear is that the OS-cache is too much in flux to get any stable numbers out
of it.

-- 
Jesper


--------------080203040905070308090406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 2011-05-16 03:18, Greg Smith wrote:<br>
    <span style="white-space: pre;">&gt; You can't do it in real-time.
      You don't necessarily want that to<br>
      &gt; even if it were possible; too many possibilities for nasty
      feedback<br>
      &gt; loops where you always favor using some marginal index that
      happens<br>
      &gt; to be in memory, and therefore never page in things that
      would be<br>
      &gt; faster once they're read. The only reasonable implementation
      that<br>
      &gt; avoids completely unstable plans is to scan this data
      periodically<br>
      &gt; and save some statistics on it--the way ANALYZE does--and
      then have<br>
      &gt; that turn into a planner input.<br>
    </span><br>
    Would that be feasible? Have process collecting the data every
    now-and-then<br>
    probably picking some conservative-average function and feeding<br>
    it into pg_stats for each index/relation? <br>
    <br>
    To me it seems like a robust and fairly trivial way to to get better
    numbers. The <br>
    fear is that the OS-cache is too much in flux to get any stable
    numbers out<br>
    of it.&nbsp; <br>
    <br>
    -- <br>
    Jesper<br>
    <br>
    <br>
  </body>
</html>

--------------080203040905070308090406--