From: Dennis Gearon <gearond@cvc.net>
To: Denis <denis@next2me.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
Date: Tue, 08 Apr 2003 13:25:44 -0700
Message-ID: <3E933048.4000805@cvc.net> (raw)
In-Reply-To: <b6v9mv$1h8q$1@news.hub.org>
References: <b6v9mv$1h8q$1@news.hub.org>
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)
>
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: gearond@cvc.net, denis@next2me.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
In-Reply-To: <3E933048.4000805@cvc.net>
* 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