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

> -----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]