From: Robert Lor <Robert.Lor@Sun.COM>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jan de Visser <jdevisser@digitalfairway.com>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*)
Date: Thu, 15 May 2008 11:55:00 -0500
Message-ID: <482C6AE4.8020607@sun.com> (raw)
In-Reply-To: <28324.1210834939@sss.pgh.pa.us>
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
<482B1D5F.EE98.0025.0@wicourts.gov>
<Pine.GSO.4.64.0805142133250.27290@westnet.com>
<20080515020553.GN9838@alvh.no-ip.org>
<Pine.GSO.4.64.0805142211300.5617@westnet.com>
<1159c1e90805141938j5f85b490rb5c1cfdf9e428bce@mail.gmail.com>
<28324.1210834939@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Robert.Lor@Sun.COM, tgl@sss.pgh.pa.us, jdevisser@digitalfairway.com, gsmith@gregsmith.com, alvherre@commandprompt.com
Subject: Re: I/O on select count(*)
In-Reply-To: <482C6AE4.8020607@sun.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox