Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4481AB5DBD1 for ; Mon, 16 May 2011 12:46:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 89858-06 for ; Mon, 16 May 2011 15:46:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 651B6B5D802 for ; Mon, 16 May 2011 12:46:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p4GFkjSE016677; Mon, 16 May 2011 11:46:45 -0400 (EDT) To: Robert Haas cc: Jesper Krogh , pgsql-performance@postgresql.org Subject: Re: reducing random_page_cost from 4 to 2 to force index scan In-reply-to: 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> Comments: In-reply-to Robert Haas message dated "Mon, 16 May 2011 10:34:12 -0400" Date: Mon, 16 May 2011 11:46:45 -0400 Message-ID: <16676.1305560805@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/243 X-Sequence-Number: 43752 Robert Haas writes: > On Mon, May 16, 2011 at 12:49 AM, Jesper Krogh wrote: >> Ok, it may not work as well with index'es, since having 1% in cache may very >> well mean that 90% of all requested blocks are there.. for tables in should >> be more trivial. > Tables can have hot spots, too. Consider a table that holds calendar > reservations. Reservations can be inserted, updated, deleted. But > typically, the most recent data will be what is most actively > modified, and the older data will be relatively more (though not > completely) static, and less frequently accessed. Such examples are > common in many real-world applications. Yes. I'm not convinced that measuring the fraction of a table or index that's in cache is really going to help us much. Historical cache hit rates might be useful, but only to the extent that the incoming query has a similar access pattern to those in the (recent?) past. It's not an easy problem. I almost wonder if we should not try to measure this at all, but instead let the DBA set a per-table or per-index number to use, analogous to the override we added recently for column n-distinct statistics ... regards, tom lane