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