From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Cannot get to use index scan on a big table!
Date: Tue, 23 Apr 2002 13:18:49 +0530
Message-ID: <3CC511E1.F19105F6@trade-india.com> (raw)
Hi Folks,
i guess by now it is the most freq. asked question on list ;-)
i have a simple SQL query and it does not seems to use index
despite its existance and VCUUMING of table.
tradein_clients=> explain select email_id from email_source where
source_id=186 ;
NOTICE: QUERY PLAN:
Seq Scan on email_source (cost=0.00..19191.50 rows=41602 width=4)
EXPLAIN
tradein_clients=>
(can anyone please explain why the figure 41602??)
i am using postgresql 7.1.2 (ples. do not curse me for not upg.)
i have done "VACUUM ANALYZE" on the table in question
the table has ~ 1.1 million records and seq scan is
killing my apps
regds
mallah.
Some more info below:
tradein_clients=> VACUUM VERBOSE ANALYZE email_source ;
NOTICE: --Relation email_source--
NOTICE: Pages 5794: Changed 1, reaped 30, Empty 0, New 0; Tup 1071800:
Vac 81, Keep/VTL 9/0, Crash 0, UnUsed 9, MinLen 40, MaxLen 40; Re-using:
Free/Avail. Space 4560/2208; EndEmpty/Avail. Pages 0/29. CPU 0.30s/0.12u
sec.
NOTICE: Index email_source_email_id: Pages 2350; Tuples 1071800:
Deleted 0. CPU 0.16s/1.08u sec.
NOTICE: Index email_source_source_id: Pages 2350; Tuples 1071800:
Deleted 0. CPU 0.14s/1.02u sec.
NOTICE: Rel email_source: Pages: 5794 --> 5794; Tuple(s) moved: 32. CPU
0.01s/0.01u sec.
NOTICE: Index email_source_email_id: Pages 2350; Tuples 1071800:
Deleted 32. CPU 0.15s/0.84u sec.
NOTICE: Index email_source_source_id: Pages 2350; Tuples 1071800:
Deleted 32. CPU 0.11s/0.79u sec.
NOTICE: Analyzing...
VACUUM
table structures:
tradein_clients=> \d email_source
Table "email_source"
Column | Type | Modifiers
-----------+---------+-----------
email_id | integer |
source_id | integer |
Indexes: email_source_email_id,
email_source_source_id
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: mallah@trade-india.com
Subject: Re: Cannot get to use index scan on a big table!
In-Reply-To: <3CC511E1.F19105F6@trade-india.com>
* 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