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
