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