Re: Ad Hoc Indexes

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 really 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
>