While all the talk of a hinting system over in hackers and perform is
good, and I have a few queries that could live with a simple hint system
pop up now and again, I keep thinking that a query planner that learns
from its mistakes over time is far more desirable.
Is it reasonable or possible for the system to have a way to look at
query plans it's run and look for obvious mistakes its made, like being
off by a factor of 10 or more in estimations, and slowly learn to apply
its own hints?
Seems to me that would be far more useful than my having to babysit the
queries that are running slow and come up with hints to have the
database do what I want.
I already log slow queries and review them once a week by running them
with explain analyze and adjust what little I can, like stats targets
and such.
It seems to me the first logical step would be having the ability to
flip a switch and when the postmaster hits a slow query, it saves both
the query that ran long, as well as the output of explain or explain
analyze or some bastardized version missing some of the inner timing
info. Even just saving the parts of the plan where the planner thought
it would get 1 row and got instead 350,000 and was using a nested loop
to join would be VERY useful. I could see something like that
eventually evolving into a self tuning system.
Well, I'm busy learning to be an Oracle DBA right now, so I can't do
it. But it would be a very cool project for the next college student
who shows up looking for one.
