Re: I/O on select count(*)

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