Skip site navigation (1) Skip section navigation (2)

Re: I/O on select count(*)

From: Robert Lor <Robert(dot)Lor(at)Sun(dot)COM>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jan de Visser <jdevisser(at)digitalfairway(dot)com>, Greg Smith <gsmith(at)gregsmith(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: I/O on select count(*)
Date: 2008-05-15 16:55:00
Message-ID: 482C6AE4.8020607@sun.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-05-15 17:42:09
Subject: Re: I/O on select count(*)
Previous:From: Subbiah Stalin-XCGF84Date: 2008-05-15 16:28:19
Subject: Re: Update performance degrades over time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group