Message-ID: <3E93348C.20208@cvc.net>
Date: Tue, 08 Apr 2003 13:43:56 -0700
From: Dennis Gearon <gearond@cvc.net>
Reply-To: gearond@cvc.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3b) Gecko/20030210
MIME-Version: 1.0
To: Dann Corbit <DCorbit@connx.com>
Cc: Denis <denis@next2me.com>, pgsql-performance@postgresql.org,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
References: 
 <D90A5A6C612A39408103E6ECDD77B829408AC5@voyager.corporate.connx.com>
In-Reply-To: 
 <D90A5A6C612A39408103E6ECDD77B829408AC5@voyager.corporate.connx.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

from mysql manual:
-------------------------------------------------------------
"COUNT(*) is optimized to return very quickly if the SELECT retrieves from one 
table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> select COUNT(*) from student;"
-------------------------------------------------------------

A nice little optimization, maybe not possible in a MVCC system.

Dann Corbit wrote:
>>-----Original Message-----
>>From: Denis [mailto:denis@next2me.com] 
>>Sent: Tuesday, April 08, 2003 12:57 PM
>>To: pgsql-performance@postgresql.org; 
>>pgsql-general@postgresql.org; pgsql-sql@postgresql.org
>>Subject: [GENERAL] Yet Another (Simple) Case of Index not used
>>
>>
>>Hi there,
>>I'm running into a quite puzzling simple example where the 
>>index I've created on a fairly big table (465K entries) is 
>>not used, against all common sense expectations: The query I 
>>am trying to do (fast) is:
>>
>>select count(*) from addresses;
>>
>>This takes more than a second to complete, because, as the 
>>'explain' command shows me, the index created on 'addresses' 
>>is not used, and a seq scan is being used. 
> 
> 
> As well it should be.
> 
> 
>>One would assume 
>>that the creation of an index would allow the counting of the 
>>number of entries in a table to be instantanous?
> 
> 
> Traversing the index to perform the count will definitely make the query
> many times slower.
> 
> A general rule of thumb (not sure if it is true with PostgreSQL) is that
> if you have to traverse more than 10% of the data with an index then a
> full table scan will be faster.  This is especially true when there is
> highly redundant data in the index fields.  If there were an index on
> bit data type, and you have half and half 1 and 0, an index scan of the
> table will be disastrous.
> 
> To simply scan the table, we will just sequentially read pages until the
> data is exhausted.  If we follow the index, we will randomly jump from
> page to page, defeating the read buffering.
> [snip]
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
>