Yet Another (Simple) Case of Index not used

From: "Denis" <denis(at)next2me(dot)com>
To: pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Yet Another (Simple) Case of Index not used
Date: 2003-04-08 19:57:16
Message-ID: b6v9mv$1h8q$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance pgsql-sql

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(at)next2me(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-04-08 20:25:44 Re: Yet Another (Simple) Case of Index not used
Previous Message Dennis Gearon 2003-04-08 19:55:35 Re: [GENERAL] Problem about Triggers and Function

Browse pgsql-performance by date

  From Date Subject
Next Message Dennis Gearon 2003-04-08 20:25:44 Re: Yet Another (Simple) Case of Index not used
Previous Message Achilleus Mantzios 2003-04-08 19:40:39 Re: help need it

Browse pgsql-sql by date

  From Date Subject
Next Message Dennis Gearon 2003-04-08 20:25:44 Re: Yet Another (Simple) Case of Index not used
Previous Message Dennis Gearon 2003-04-08 19:55:35 Re: [GENERAL] Problem about Triggers and Function