Re: [GENERAL] Yet Another (Simple) Case of Index not used

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
>