From: Denis <denis@next2me.com>
To: pgsql-performance@postgresql.org
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Yet Another (Simple) Case of Index not used
Date: Tue, 8 Apr 2003 12:57:16 -0700
Message-ID: <b6v9mv$1h8q$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: denis@next2me.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
In-Reply-To: <b6v9mv$1h8q$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox