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@emproshunts.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