Re: count(*) v. count(pk)

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Wed, 29 Apr 1998 22:59:07 GMT
Message-ID: <3547b016.10922560@www.sigov.si>

On Tue, 28 Apr 1998 14:11:43 -0600, bialik_at_wis.weizmann.ac.il wrote:

>Hi.
>
>I run a number of test concerning the performance of COUNT(*) and a number
>of different options.
>
>1. The bottom line : COUNT(*) is the best way to get the number of rows in
> the table.
>2. It is possible to force INDEX SCAN by using PK-fld > 0 in WHERE clause,
> but the performance suffers.
>I think that the main reason for bad performance is an execution of
>SORT(AGGREGATE).
>
>Following the results of test ( starting with worst case and going to the
>best one ).
> ...[SNIP]...
From your trace results it's obvious that your index occupies more database blocks than your table (i.e. the rowids in the index take more space than the non-indexed columns in the table). Apparently your index occupies aproximately 42.000 blocks while you table fills only around 40.000 blocks. So it is naturaly that in your case index scan will take more time than table scan. In praxis however individual indexes usualy take much less space than their underlying tables and their full scans can be much quicker than table full scans.

What surprises me is the fact that COUNT(*) is realy faster than COUNT(anything_else) - I tested that on some of my test tables and COUNT(*) was approximately 10% faster. I allways thought it is the other way around, because I assumed that with COUNT(*) the parser will have some extra work identifying all the table columns from the dictionary. Can anyone explain where does this execution time difference between COUNT(*) and COUNT(anything_else) come from?

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Apr 29 1998 - 17:59:07 CDT