Yet Another (Simple) Case of Index not used
От | Denis |
---|---|
Тема | Yet Another (Simple) Case of Index not used |
Дата | |
Msg-id | b6v96q$1fgm$1@news.hub.org обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: