Question: We have recently moved PostgreSql as we just migrated to
OpenMfg. i really like the database and PL/pgSQL its very similar to
how FoxPro language layout so learning has been pretty easy.
Now for my question It does not appear PostgreSQL does not have an Ad
Hoc Indexes ability where the Query planner will create an in memory
index based on the Select, Update, Insert or Delete commands. I got
pretty lazy with MsSQL and Foxpro doing ad hoc indexes to speed up
complex queries where i should have created an index but never did.
Is there any plans in the future to add the ability for PostgreSQL to
create Ad Hoc indexes if it makes sense. Example i was creating a new
report thats presently only processing around 120,000 records in the 7
tables for the query. But the query took between 18 to 19 seconds to
run. I analyzed the query and added 2 indexes and drop the processing
time to 93 milliseconds.
This query will be run 3 to 5 times a day so it makes sense to create
indexes
But with reports that run maybe once a week or once a month it does not
make sense to create indexes. this is where I think Add Hoc indexes are
nice to have because to my understanding maintaining indexes can get
pretty expensive.
Whats the likely hood of Ad Hoc Indexes being added to PostgreSql
thanks