Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8FD23B5DBE7 for ; Thu, 19 May 2011 00:00:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 99056-07 for ; Thu, 19 May 2011 03:00:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9]) by mail.postgresql.org (Postfix) with ESMTP id ACBF2B5DBDE for ; Thu, 19 May 2011 00:00:47 -0300 (ADT) Received: from [192.168.15.114] ([24.114.252.232]) by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis) id 0MKt72-1QMtTV2zCq-000lRg; Thu, 19 May 2011 05:00:34 +0200 Message-ID: <4DD487CF.8010003@2ndquadrant.com> Date: Wed, 18 May 2011 23:00:31 -0400 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Jim Nasby CC: Tom Lane , Robert Haas , Jesper Krogh , pgsql-performance@postgresql.org Subject: Re: reducing random_page_cost from 4 to 2 to force index scan References: <4DB6F4BB020000250003CEB9@gw.wicourts.gov> <2823.1305314437@sss.pgh.pa.us> <4DCD90F5.2010501@agliodbs.com> <4DD0195A.9040305@agliodbs.com> <4DD07855.8080907@postnewspapers.com.au> <4DD07B4E.1020503@2ndquadrant.com> <4DD0AB16.5060808@krogh.cc> <4DD0ACD0.1090801@krogh.cc> <16676.1305560805@sss.pgh.pa.us> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:L+uWDr/CpflpBmwJNTDRnZNrMBb6REFvz0SgTMhopF4 4dlZ9WmupL3aVmJasPbAd1zcVkjYDqfLb2/F25zJW3zJlXMk5A ntXX9CBPKLi6i4mtuiHdWj8wReITEJ0M8sdH9foGEv4BMsMWOM Lvg/DLqqEtMaDU+Cw4jVGplJ5b1aFi/s741V1Zepcs2XtewnY5 Z9T3dCXdUaZEA1N3HBiWOudPdwC/HlofVpg0J+EeiI= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.13 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_SORBS_WEB=0.77 X-Spam-Level: X-Archive-Number: 201105/272 X-Sequence-Number: 43781 Jim Nasby wrote: > I think the challenge there would be how to define the scope of the hot-spot. Is it the last X pages? Last X serial values? Something like correlation? > > Hmm... it would be interesting if we had average relation access times for each stats bucket on a per-column basis; that would give the planner a better idea of how much IO overhead there would be for a given WHERE clause You've already given one reasonable first answer to your question here. If you defined a usage counter for each histogram bucket, and incremented that each time something from it was touched, that could lead to a very rough way to determine access distribution. Compute a ratio of the counts in those buckets, then have an estimate of the total cached percentage; multiplying the two will give you an idea how much of that specific bucket might be in memory. It's not perfect, and you need to incorporate some sort of aging method to it (probably weighted average based), but the basic idea could work. -- 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