From mallah@trade-india.com Tue Apr 23 07:45:27 2002 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-- From sszabo@megazone23.bigpanda.com Tue Apr 23 15:00:58 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 2CAB7475469 for ; Tue, 23 Apr 2002 11:00:58 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C675FD610; Tue, 23 Apr 2002 08:01:02 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id BC1A35C02; Tue, 23 Apr 2002 08:01:02 -0700 (PDT) Date: Tue, 23 Apr 2002 08:01:02 -0700 (PDT) From: Stephan Szabo To: Rajesh Kumar Mallah Cc: PostgResql SQL Mailing List Subject: Re: Cannot get to use index scan on a big table! In-Reply-To: <3CC511E1.F19105F6@trade-india.com> Message-ID: <20020423075534.S13153-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/278 X-Sequence-Number: 7542 On Tue, 23 Apr 2002, Rajesh Kumar Mallah wrote: > 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??) That's the estimated number of matching rows. I'd guess that this number is an over estimate (how many rows actually are returned?) Do you have any particularly frequent values of source_id that are much more common than others? 7.1 and earlier had problems with over estimating the number of matching rows when the distribution had a very uneven distribution of values, select * from pg_statistic where starelid=(select oid from pg_class where relname='email_source') should give the stored statistics from the analyze. As a comparison, if you do "set enable_seqscan=off;" and then do the query and explain, what does it give for the costs there, and does it take less time? From mallah@trade-india.com Wed Apr 24 05:53:59 2002 Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 2B070474E71 for ; Wed, 24 Apr 2002 01:53:59 -0400 (EDT) Received: (qmail 11945 invoked from network); 24 Apr 2002 05:47:04 -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; 24 Apr 2002 05:47:04 -0000 Message-ID: <3CC64943.1FECC6D8@trade-india.com> Date: Wed, 24 Apr 2002 11:27:23 +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: Stephan Szabo Cc: PostgResql SQL Mailing List Subject: Re: Cannot get to use index scan on a big table! References: <20020423075534.S13153-100000@megazone23.bigpanda.com> Content-Type: multipart/alternative; boundary="------------2938ADC7FD067F052B3DDFCD" X-Archive-Number: 200204/285 X-Sequence-Number: 7549 --------------2938ADC7FD067F052B3DDFCD Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Stephan Szabo wrote: The actual now nos rows is 10 for source_id=186 > > Do you have any particularly frequent values of source_id that are much > more common than others? 7.1 yes you are right freq. of source_id are *quite* varied. from 700,000 to 10 :-( but except for the most frequent avg. freq. may be 5000 > and earlier had problems with over > estimating the number of matching rows when the distribution had a > very uneven distribution of values, select * from pg_statistic where > starelid=(select oid from pg_class where relname='email_source') > should give the stored statistics from the analyze. select * from pg_statistic where starelid=(select oid from pg_class where relname='email_source'); starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival ----------+-----------+-------+-------------+---------------+--------------+----------+---------- 31548 | 1 | 97 | 0 | 5.59811e-06 | 53872 | 2 | 1626629 31548 | 2 | 97 | 0 | 0.611849 | 156 | 1 | 186 (2 rows) AFTER VACUUM ANALYINZING JUST NOW! starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival ----------+-----------+-------+-------------+---------------+--------------+----------+---------- 31548 | 1 | 97 | 0 | 6.4078e-06 | 53872 | 2 | 1629500 31548 | 2 | 97 | 0 | 0.600296 | 156 | 1 | 190 (2 rows) > > > As a comparison, if you do "set enable_seqscan=off;" and then do > the query and explain, what does it give for the costs there, and > does it take less time? Still its overestimated , but its much faster explain select count(email_id) from email_source where source_id=186; NOTICE: QUERY PLAN: Aggregate (cost=46798.14..46798.14 rows=1 width=4) -> Index Scan using email_source_source_id on email_source (cost=0.00..46688.98 rows=43664 width=4) EXPLAIN Stephan thanks for the reply, i think my question has been adequetely answered and i conclude that i shud. upgrade my PG setup without wasting my/others' time. regds mallah. --------------2938ADC7FD067F052B3DDFCD Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Stephan Szabo wrote:

The actual now nos  rows is 10 for source_id=186

 
Do you have any particularly frequent values of source_id that are much
more common than others?  7.1
yes you are right  freq. of source_id are *quite* varied. from  700,000
to 10  :-(  but except  for the most frequent  avg. freq. may be 5000
 
and earlier had problems with over
estimating the number of matching rows when the distribution had a
very uneven distribution of values, select * from pg_statistic where
starelid=(select oid from pg_class where relname='email_source')
should give the stored statistics from the analyze.
select * from pg_statistic  where starelid=(select oid from pg_class where relname='email_source');
 starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival
----------+-----------+-------+-------------+---------------+--------------+----------+----------
    31548 |         1 |    97 |           0 |   5.59811e-06 | 53872        | 2        | 1626629
    31548 |         2 |    97 |           0 |      0.611849 | 156          | 1        | 186
(2 rows)

AFTER VACUUM ANALYINZING JUST NOW!
 

 starelid | staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival
----------+-----------+-------+-------------+---------------+--------------+----------+----------
    31548 |         1 |    97 |           0 |    6.4078e-06 | 53872        | 2        | 1629500
    31548 |         2 |    97 |           0 |      0.600296 | 156          | 1        | 190
(2 rows)
 

 

As a comparison, if you do "set enable_seqscan=off;" and then do
the query and explain, what does it give for the costs there, and
does it take less time?

Still its overestimated  , but its  much faster

 explain  select count(email_id) from email_source  where source_id=186;

NOTICE:  QUERY PLAN:

Aggregate  (cost=46798.14..46798.14 rows=1 width=4)
  ->  Index Scan using email_source_source_id on email_source  (cost=0.00..46688.98 rows=43664 width=4)

EXPLAIN
 

Stephan thanks for the reply,
i think my question has been adequetely answered and i conclude
that i shud. upgrade my PG setup without wasting my/others' time.
 

regds
mallah. --------------2938ADC7FD067F052B3DDFCD--