Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 3EDF84759A2 for ; Tue, 23 Apr 2002 03:45:27 -0400 (EDT) Received: (qmail 5822 invoked from network); 23 Apr 2002 07:38:39 -0000 Received: from system11.trade-india-local.com (HELO trade-india.com) (192.168.0.11) by ptil-236-129-del.primus-india.net with SMTP; 23 Apr 2002 07:38:39 -0000 Message-ID: <3CC511E1.F19105F6@trade-india.com> Date: Tue, 23 Apr 2002 13:18:49 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgResql SQL Mailing List Subject: Cannot get to use index scan on a big table! Content-Type: multipart/alternative; boundary="------------CD5F59328E72378574881F01" X-Archive-Number: 200204/273 X-Sequence-Number: 7537 --------------CD5F59328E72378574881F01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------CD5F59328E72378574881F01 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------CD5F59328E72378574881F01--