Re: A query planner that learns

On Thu, Oct 12, 2006 at 03:31:50PM -0500, 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?
>
> 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.

Saves it and then... does what? That's the whole key...

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

Why? There's a huge demand for PostgreSQL experts out there... or is
this for a current job?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)