Scott Marlowe wrote:
> 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?
Technically it is very feasible. But I think you might want to check US
Patent 6,763,359 before you start writing any code.
> 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.
I think it would be a good start if we can specify a
log_selectivity_error_threshold and if estimates are more then that
factor off, the query, parameters and planner estimates get logged for
later analysis. That would be driven entirely by selectivity estimates
and not (estimated) cost since cost is influenced by outside factors
such as other processes competing for resources. If a system for
statistical hints emerges from the current discussion we would indeed
have the input to start tuning the selectivity estimations.
Jochem