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