Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9878B1337B55 for ; Fri, 24 Sep 2010 16:25:05 -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 52816-01 for ; Fri, 24 Sep 2010 19:24:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outbound.afilias.info (outbound.afilias.info [69.46.124.26]) by mail.postgresql.org (Postfix) with ESMTP id 2E88C133798E for ; Fri, 24 Sep 2010 16:24:54 -0300 (ADT) Received: from ms5.yyz2.afilias-ops.info ([10.50.129.111] helo=smtp.afilias.info) by outbound.afilias.info with esmtp (Exim 4.69) (envelope-from ) id 1OzDt8-0006XS-5Z for pgsql-performance@postgresql.org; Fri, 24 Sep 2010 19:24:54 +0000 Received: from tor-gateway.afilias.info ([199.15.87.4] helo=[10.10.46.12]) by smtp.afilias.info with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1OzDt8-00052U-56 for pgsql-performance@postgresql.org; Fri, 24 Sep 2010 19:24:54 +0000 Message-ID: <4C9CFB06.8090403@ca.afilias.info> Date: Fri, 24 Sep 2010 15:24:54 -0400 From: Brad Nicholson User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.9) Gecko/20100920 Lightning/1.0b2 Thunderbird/3.1.4 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Memory usage - indexes References: <173228.29370.qm@web39701.mail.mud.yahoo.com> In-Reply-To: <173228.29370.qm@web39701.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/177 X-Sequence-Number: 40380 On 10-09-24 03:06 PM, Bob Lunney wrote: > The pertinent difference between pg_stat_user_indexes and pg_statio_user_indexes is the latter shows the number of blocks read from disk or found in the cache. I have a minor, but very important correction involving this point. The pg_statio tables show you what blocks are found in the Postgres buffer cache, and what ones are not. For the ones that are not, those blocks may come from the OS filesystem cache, a battery backed cache, or on the actual disk. There is a big difference in performance based on where you are actually getting those blocks from (and you can't this info from Postgres). -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.