Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C03B62E0052 for ; Thu, 15 May 2008 13:53:48 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40148-02-3 for ; Thu, 15 May 2008 13:53:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sca-es-mail-1.sun.com (sca-es-mail-1.Sun.COM [192.18.43.132]) by developer.postgresql.org (Postfix) with ESMTP id 224382E007B for ; Thu, 15 May 2008 13:53:26 -0300 (ADT) Received: from fe-sfbay-09.sun.com ([192.18.43.129]) by sca-es-mail-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m4FGrOG3022602 for ; Thu, 15 May 2008 09:53:24 -0700 (PDT) Received: from conversion-daemon.fe-sfbay-09.sun.com by fe-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0K0X00H014JZNN00@fe-sfbay-09.sun.com> (original mail from Robert.Lor@Sun.COM) for pgsql-performance@postgresql.org; Thu, 15 May 2008 09:53:24 -0700 (PDT) Received: from [192.168.1.108] ([70.253.94.223]) by fe-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0K0X00MY05KN6BA0@fe-sfbay-09.sun.com>; Thu, 15 May 2008 09:53:12 -0700 (PDT) Date: Thu, 15 May 2008 11:55:00 -0500 From: Robert Lor Subject: Re: I/O on select count(*) In-reply-to: <28324.1210834939@sss.pgh.pa.us> To: Tom Lane Cc: Jan de Visser , Greg Smith , Alvaro Herrera , pgsql-performance@postgresql.org Message-id: <482C6AE4.8020607@sun.com> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <920841.71690.qm@web44808.mail.sp1.yahoo.com> <482B1D5F.EE98.0025.0@wicourts.gov> <20080515020553.GN9838@alvh.no-ip.org> <1159c1e90805141938j5f85b490rb5c1cfdf9e428bce@mail.gmail.com> <28324.1210834939@sss.pgh.pa.us> User-Agent: Thunderbird 2.0.0.6 (X11/20071119) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/226 X-Sequence-Number: 30194 Tom Lane wrote: > It's certainly true that hint-bit updates cost something, but > quantifying how much isn't easy. Maybe we can instrument the code with DTrace probes to quantify the actual costs. I'm not familiar with the code, but if I know where to place the probes, I can easily do a quick test and provide the data. > The off-the-cuff answer is to do the > select count(*) twice and see how much cheaper the second one is. Doesn't seem the second run is cheaper as shown in the results below. The data came from the probes I've added recently. *************** Run #1 ********************** SQL Statement : select count(*) from accounts; Execution time : 1086.58 (ms) ============ Buffer Read Counts ============ Tablespace Database Table Count 1663 16384 1247 1 1663 16384 2600 1 1663 16384 2703 1 1663 16384 1255 2 1663 16384 2650 2 1663 16384 2690 3 1663 16384 2691 3 1663 16384 16397 8390 ======== Dirty Buffer Write Counts ========= Tablespace Database Table Count 1663 16384 16397 2865 Total buffer cache hits : 1932 Total buffer cache misses : 6471 Average read time from cache : 5638 (ns) Average read time from disk : 143371 (ns) Average write time to disk : 20368 (ns) *************** Run #2 ********************** SQL Statement : select count(*) from accounts; Execution time : 1115.94 (ms) ============ Buffer Read Counts ============ Tablespace Database Table Count 1663 16384 16397 8390 ======== Dirty Buffer Write Counts ========= Tablespace Database Table Count 1663 16384 16397 2865 Total buffer cache hits : 1931 Total buffer cache misses : 6459 Average read time from cache : 4357 (ns) Average read time from disk : 154127 (ns) Average write time to disk : 20368 (ns) -Robert