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@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
