Cannot get to use index scan on a big table!

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: PostgResql SQL Mailing List <pgsql-sql(at)postgresql(dot)org>
Subject: Cannot get to use index scan on a big table!
Date: 2002-04-23 07:48:49
Message-ID: 3CC511E1.F19105F6@trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ian Cass 2002-04-23 09:35:59 Date indexing
Previous Message Ian Morgan 2002-04-23 06:54:31 How to discover foreign keys (without pulling hair out)