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

as I remember, mysql keeps the record count in a variable and is instantaneaous
with that kind of query. Recent posts suggest the Postgres does not keep that
variable and has to do the seq scan.

Denis wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>