Message-ID: <3E933048.4000805@cvc.net>
Date: Tue, 08 Apr 2003 13:25:44 -0700
From: Dennis Gearon <gearond@cvc.net>
Reply-To: gearond@cvc.net
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3b) Gecko/20030210
MIME-Version: 1.0
To: Denis <denis@next2me.com>
Cc: pgsql-performance@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
References: <b6v9mv$1h8q$1@news.hub.org>
In-Reply-To: <b6v9mv$1h8q$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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)
>