Re: Ad Hoc Indexes

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. I wish i
could do an Explain on it with create index in the query but i can't it
errors out. So i reran the query with indexes already in place it drops
the query time 191 milliseconds.

Create and deleting the indexes on the fly improves performance almost
50 times. I think creating Ad Hoc indexes on the fly in memory makes
sense . I imagine it would be even faster if the index stayed in memory

Tom Lane wrote:
> Justin <justin(at)emproshunts(dot)com> writes:
>
>> Is there any plans in the future to add the ability for PostgreSQL to
>> create Ad Hoc indexes if it makes sense.
>>
>
> No, I'm not aware of anyone contemplating such a thing. I can hardly
> imagine a situation where building an index for a single query is
> actually a win. Maybe those DBMSes you mention were using this as a
> substitute for having decent join mechanisms, or something?
>
> regards, tom lane
>