From: "Denis" <denis@next2me.com>
Subject: Yet Another (Simple) Case of Index not used
Date: Tue, 8 Apr 2003 12:57:16 -0700
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 68
Message-ID: <b6v9mv$1h8q$1@news.hub.org>
To: pgsql-performance@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org

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.
One would assume that the creation of an index would allow the counting of
the number of entries in a table to be instantanous?

Here are the details:

* Using the latest postgresql 7.3.2 release, built and installed from
sources on a Linux box, under Red Hat 8.0

* I have an 'addresses' table defined as:
Columm         |     Type
-------------------------------
address          |  text
city                 |  char var (20)
zip                  |  char var (5)
state               |  char var (2)
Unique keys: addresses_idx

* I have created a unique index 'addresses_idx' on (address, city, zip,
state):
\d addresses_idx;
Index "addresses_idx"
Columm         |     Type
-------------------------------
address          |  text
city                 |  char var (20)
zip                  |  char var (5)
state               |  char var (2)
unique btree

* I did (re)create the index several times
* I did run the vacuum analyse command several times
* I forced enable_indexscan to true
* I forced enable_seqscan to false

Despite of all of this, each time I try:
===> explain select count(*) from addresses;
I get the following:
===> NOTICE: QUERY PLAN:
===>
===> Aggregate (cost=100012799.89..100012799.89 rows=1 width=0)
===> -> Seq Scan on addresses (cost=100000000.00..100011635.11 rows=465911
width=0)

Quite puzzling, isn't it?
I've searched a bunch of mailing lists and websites, and found many reports
of special cases where it could be argued that the planner may have had a
case for choosing seq scanning over idx scanning, but unless I am missing
some fundamental concept, there's something wrong here.
Any suggestion anyone?
Thanks,

Denis
denis@next2me.com