Re: Ad Hoc Indexes

oops dam spell checker really should be rarely sorry <br /><br /> Justin wrote: <blockquote
cite="mid:47BA01D7.9010001@emproshunts.com"type="cite"></blockquote> Then why are the estimates so far off???  If
estimateswhere correct would it improve the performance that much.  <br /><br /> Vaccum is set to run automatically so
thestats stay update. <br /><br /> Total record count for the tables for all the tables put together is around 120,000
thequery returns only 458 records which is correct.  <br /><br /> If i am correct in my understanding the reason the
indeximproved the query so much is the wooper table gets hit hard because it appears in 3 separate nested queries . So
takingonly  458 records returned from the parent query times 3 for 1,375 table scans going through 21,873 records for a
totalnumber records being processed to 30,075,375  on a table with no index.  So if you look at it that way PostgreSql
didremarkably well processing the query in 18 to 20 seconds.    <br /><br /> The idea behind adhoc indexes is when one
shotqueries or rarely used queries are created that would require numerous indexes to run in a decent time can be run
ina faction of the time.  This also saves processing times across the entire system where creating indexes for the all
thepossible queries is impractical <br /><br /> This does not take away the need for index but speed up  ad-hoc queries
createdfrom a website or other business analysis tool that someone might create <br /><br /> Tom Lane wrote:
<blockquotecite="mid:1552.1203370105@sss.pgh.pa.us" type="cite"><pre wrap="">Justin <a class="moz-txt-link-rfc2396E"
href="mailto:justin@emproshunts.com"moz-do-not-send="true"><justin@emproshunts.com></a> writes: </pre><blockquote
type="cite"><prewrap="">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"
moz-do-not-send="true">http://www.postgresql.org/docs/faq</a></pre></blockquote>