Re: Cannot get to use index scan on a big table!

Stephan Szabo wrote: <p>The actual now nos  rows is 10 for source_id=186 <blockquote type="CITE">  <br />Do you have
anyparticularly frequent values of source_id that are much <br />more common than others?  7.1</blockquote> yes you are
right freq. of source_id are *quite* varied. from  700,000 <br />to 10  :-(  but except  for the most frequent  avg.
freq.may be 5000 <br />  <blockquote type="CITE">and earlier had problems with over <br />estimating the number of
matchingrows when the distribution had a <br />very uneven distribution of values, select * from pg_statistic where <br
/>starelid=(selectoid from pg_class where relname='email_source') <br />should give the stored statistics from the
analyze.</blockquote><tt><fontcolor="#000099"><font size="-1">select * from pg_statistic  where starelid=(select oid
frompg_class where relname='email_source');</font></font></tt><br /><tt><font color="#000099"><font size="-1"> starelid
|staattnum | staop | stanullfrac | stacommonfrac | stacommonval | staloval | stahival</font></font></tt><br /><tt><font
color="#000099"><font
size="-1">----------+-----------+-------+-------------+---------------+--------------+----------+----------</font></font></tt><br
/><tt><fontcolor="#000099"><font size="-1">    31548 |         1 |    97 |           0 |   5.59811e-06 | 53872        |
2       | 1626629</font></font></tt><br /><tt><font color="#000099"><font size="-1">    31548 |         2 |    97
|          0 |      0.611849 | 156          | 1        | 186</font></font></tt><br /><tt><font color="#000099"><font
size="-1">(2rows)</font></font></tt><p><b><tt><font size="-2">AFTER VACUUM ANALYINZING JUST NOW!</font></tt></b><br
/><b><tt><fontsize="-2"></font></tt></b>  <p><tt><font color="#000099"><font size="-1"> starelid | staattnum | staop |
stanullfrac| stacommonfrac | stacommonval | staloval | stahival</font></font></tt><br /><tt><font color="#000099"><font
size="-1">----------+-----------+-------+-------------+---------------+--------------+----------+----------</font></font></tt><br
/><tt><fontcolor="#000099"><font size="-1">    31548 |         1 |    97 |           0 |    6.4078e-06 | 53872        |
2       | 1629500</font></font></tt><br /><tt><font color="#000099"><font size="-1">    31548 |         2 |    97
|          0 |      0.600296 | 156          | 1        | 190</font></font></tt><br /><tt><font color="#000099"><font
size="-1">(2rows)</font></font></tt><br />  <blockquote type="CITE">  <p>As a comparison, if you do "set
enable_seqscan=off;"and then do <br />the query and explain, what does it give for the costs there, and <br />does it
takeless time?</blockquote> Still its overestimated  , but its  much faster <p><tt><font color="#000099"><font
size="-1"> explain select count(email_id) from email_source  where source_id=186;</font></font></tt><p><tt><font
color="#000099"><fontsize="-1">NOTICE:  QUERY PLAN:</font></font></tt><tt><font color="#000099"><font
size="-1"></font></font></tt><p><tt><fontcolor="#000099"><font size="-1">Aggregate  (cost=46798.14..46798.14 rows=1
width=4)</font></font></tt><br/><tt><font color="#000099"><font size="-1">  ->  Index Scan using
email_source_source_idon email_source  (cost=0.00..46688.98 rows=43664 width=4)</font></font></tt><tt><font
color="#000099"><fontsize="-1"></font></font></tt><p><tt><font color="#000099"><font
size="-1">EXPLAIN</font></font></tt><br/><tt><font color="#000099"><font size="-1"></font></font></tt> <tt><font
color="#000099"><fontsize="-1"></font></font></tt><p><font color="#000000">Stephan thanks for the reply,</font><br
/><fontcolor="#000000">i think my question has been adequetely answered and i conclude</font><br /><font
color="#000000">thati shud. upgrade my PG setup without wasting my/others' time.</font><br /><font
color="#000000"></font> <fontcolor="#000000"></font><p><font color="#000000">regds</font><br /><font
color="#000000">mallah.</font>