pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (3+ messages)  latest in thread

Message-ID: <3CC511E1.F19105F6@trade-india.com>
Permalink:  ../3CC511E1.F19105F6@trade-india.com/
Also on:    postgresql.org/message-id/3CC511E1.F19105F6@trade-india.com

 · 

reply

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