Re: Ad Hoc Indexes

Then why are the estimates so far off???  If estimates where correct would it improve the performance that much.  <br
/><br/> Vaccum is set to run automatically so the stats stay update. <br /><br /> Total record count for the tables for
allthe tables put together is around 120,000 the query returns only 458 records which is correct.  <br /><br /> If i am
correctin my understanding the reason the index improved the query so much is the wooper table gets hit hard because it
appearsin 3 separate nested queries . So taking only  458 records returned from the parent query times 3 for 1,375
tablescans going through 21,873 records for a total number records being processed to 30,075,375  on a table with no
index. So if you look at it that way PostgreSql did remarkably well processing the query in 18 to 20 seconds.    <br
/><br/> The idea behind adhoc indexes is when one shot queries or really used queries are created that would require
numerousindexes to run in a decent time can be run in a faction of the time.  This also saves processing times across
theentire system where creating indexes for the all the possible queries is impractical <br /><br /> This does not take
awaythe need for index but speed up  ad-hoc queries created from a website or other business analysis tool that someone
mightcreate <br /><br /> Tom Lane wrote: <blockquote cite="mid:1552.1203370105@sss.pgh.pa.us" type="cite"><pre
wrap="">Justin<a class="moz-txt-link-rfc2396E" href="mailto:justin@emproshunts.com"><justin@emproshunts.com></a>
writes:</pre><blockquote type="cite"><pre wrap="">The idea of ad hoc indexes is speed up loop scans  To prove my idea i

created a sql file in PGAdmin that creates the indexes on the fly then 
runs the query then drops the indexs.   </pre></blockquote><pre wrap=""> </pre><blockquote type="cite"><pre wrap="">
withoutthe indexes it takes 18 to 19 seconds to run the query.   </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">To create the index and do the query takes 400 milliseconds.   </pre></blockquote><pre
wrap="">
The example you show doesn't convince me of much of anything, because
the estimated rowcounts are so far off.  I think you're basically
dealing with an estimation failure and it's pure luck that the extra
index fixes it.
        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
              <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a></pre></blockquote>