Re: Cannot get to use index scan on a big table! - Mailing list pgsql-sql
From | Rajesh Kumar Mallah |
---|---|
Subject | Re: Cannot get to use index scan on a big table! |
Date | |
Msg-id | 3CC64943.1FECC6D8@trade-india.com Whole thread Raw |
In response to | Re: Cannot get to use index scan on a big table! (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
List | pgsql-sql |
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>