Re: A query planner that learns

Forgive me if I'm way off here as I'm not all that familiar with the
internals of postgres, but isn't this what the genetic query optimizer
discussed the one of the manual's appendixes is supposed to do. Or, is
that more about using the the known costs of atomic operations that make
up a query plan, i.e. more of a bottom up approach than what you're
discussing. If it's the latter, then it sounds like what you're looking
for is a classifier system. See <a
href="http://en.wikipedia.org/wiki/Learning_classifier_system">this</a>
wikepedia article for a short description of them along with a couple of
reference links, you can google for many more.

Scott Marlowe wrote:
> On Thu, 2006-10-12 at 17:14, Jim C. Nasby wrote:
>
>> 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...
>>
>
> It's meant as a first step. I could certainly use a daily report on
> which queries had bad plans so I'd know which ones to investigate
> without having to run them each myself in explain analyze. Again, my
> point was to do it incrementally. This is something someone could do
> now, and someone could build on later.
>
> To start with, it does nothing. Just saves it for the DBA to look at.
> Later, it could feed any number of the different hinting systems people
> have been proposing.
>
> It may well be that by first looking at the data collected from problems
> queries, the solution for how to adjust the planner becomes more
> obvious.
>
>
>>> 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?
>>
>
> Long story. I do get to do lots of pgsql stuff. But right now I'm
> learning Oracle as well, cause we use both DBs. It's just that since I
> know pgsql pretty well, and know oracle hardly at all, Oracle is taking
> up lots more of my time.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)