Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 59A33B5DC13 for ; Mon, 16 May 2011 14:25:03 -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 58817-07 for ; Mon, 16 May 2011 17:24:56 +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 E5E97B5DC0E for ; Mon, 16 May 2011 14:24:55 -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 p4GHOrW8018338; Mon, 16 May 2011 13:24:53 -0400 (EDT) To: Jeff Janes 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 Jeff Janes message dated "Mon, 16 May 2011 09:45:28 -0700" Date: Mon, 16 May 2011 13:24:53 -0400 Message-ID: <18337.1305566693@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/247 X-Sequence-Number: 43756 Jeff Janes writes: > On Sun, May 15, 2011 at 9:49 PM, 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. > Why would the index have a meaningful hot-spot unless the underlying > table had one as well? (Of course the root block will be a hot-spot, > but certainly not 90% of all requests) The accesses to an index are far more likely to be clustered than the accesses to the underlying table, because the index is organized in a way that's application-meaningful and the table not so much. Continuing the earlier example of a timestamp column, accesses might preferentially hit near the right end of the index while the underlying rows are all over the table. IOW, hot spots measured at the row level and hot spots measured at the page level could very easily be different between table and index. regards, tom lane