X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id E69A611F6256 for ; Thu, 13 Apr 2006 15:49:03 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.375 X-Spam-Level: X-Spam-Status: No, score=-2.375 required=5 tests=[AWL=0.225, BAYES_00=-2.599, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id HMoL1Rz6lqCQ for ; Thu, 13 Apr 2006 15:48:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id BC61F11F6254 for ; Thu, 13 Apr 2006 15:48:55 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Thu, 13 Apr 2006 13:48:54 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 13 Apr 2006 13:48:53 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 13 Apr 2006 13:48:53 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: Blocks read for index scans Date: Thu, 13 Apr 2006 13:48:22 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099ED1@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Blocks read for index scans Thread-Index: AcZfKdkaUpzKqAZAQiWynR0Bp6V5LgAAB37w From: "Jim Nasby" To: "Steve Poe" Cc: "Pgsql-Performance \(E-mail\)" X-OriginalArrivalTime: 13 Apr 2006 18:48:53.0407 (UTC) FILETIME=[E94D72F0:01C65F2A] X-Archive-Number: 200604/312 X-Sequence-Number: 18396 Adding -performance back in... > From: Steve Poe [mailto:steve.poe@gmail.com] > Jim, >=20 > I could be way off, but doesn't from pg_statio_user_tables=20 > contain this > information? http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html#MONI= TORING-STATS-VIEWS states: "numbers of disk blocks read and buffer hits in all indexes of that = table" That leads me to believe that it's only tracking index blocks read, and = not heap blocks read. One could presume that each index row read as = reported by pg_stat_all_tables would represent a heap block read, but a = large number of those would (hopefully) have already been in = shared_buffers. > On Thu, 2006-04-13 at 13:00 -0500, Jim Nasby wrote: > > While working on determining a good stripe size for a database, I =20 > > realized it would be handy to know what the average request=20 > size is. =20 > > Getting this info is a simple matter of joining pg_stat_all_tables =20 > > and pg_statio_all_tables and doing some math, but there's=20 > one issue =20 > > I've found; it appears that there's no information on how=20 > many heap =20 > > blocks were read in by an index scan. Is there any way to=20 > get that info? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461