Re: Ad Hoc Indexes

oops dam spell checker really should be rarely sorry

Justin wrote:
> Then why are the estimates so far off??? If estimates where correct
> would it improve the performance that much.
>
> Vaccum is set to run automatically so the stats stay update.
>
> Total record count for the tables for all the tables put together is
> around 120,000 the query returns only 458 records which is correct.
>
> If i am correct in my understanding the reason the index improved the
> query so much is the wooper table gets hit hard because it appears in
> 3 separate nested queries . So taking only 458 records returned from
> the parent query times 3 for 1,375 table scans 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.
>
> The idea behind adhoc indexes is when one shot queries or rarely used
> queries are created that would require numerous indexes to run in a
> decent time can be run in a faction of the time. This also saves
> processing times across the entire system where creating indexes for
> the all the possible queries is impractical
>
> This does not take away the need for index but speed up ad-hoc
> queries created from a website or other business analysis tool that
> someone might create
>
> Tom Lane wrote:
>> Justin <justin(at)emproshunts(dot)com> writes:
>>
>>> 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.
>>>
>>
>>
>>> without the indexes it takes 18 to 19 seconds to run the query.
>>>
>>
>>
>>> To create the index and do the query takes 400 milliseconds.
>>>
>>
>> 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?
>>
>> http://www.postgresql.org/docs/faq
>>