On Thu, Oct 12, 2006 at 04:55:17PM +0100, Heikki Linnakangas wrote: > Bruce Momjian wrote: > >Because DB2 doesn't like hints, and the fact that they have gotten to a > >point where they feel they do not need them, I feel we too can get to a > >point where we don't need them either. The question is whether we can > >get there quickly enough for our userbase. > > In all fairness, when I used to work with DB2 we often had to rewrite > queries to persuade the planner to choose a different plan. Often it was > more of an issue of plan stability; a query would suddenly become > horribly slow in production because a table had grown slowly to the > point that it chose a different plan than before. Then we had to modify > the query again, or manually set the statistics. In extreme cases we had > to split a query to multiple parts and use temporary tables and move > logic to the application to get a query to perform consistently and fast > enough. I really really missed hints. Oracle has an interesting way to deal with this, in that you can store a plan that the optimizer generates and tell it to always use it for that query. There's some other management tools built on top of that. I don't know how commonly it's used, though... Also, on the DB2 argument... I'm wondering what happens when people end up with a query that they can't get to execute the way it should? Is the planner *that* good that it never happens? Do you have to wait for a fixpack when it does happen? I'm all for having a super-smart planner, but I'm highly doubtful it will always know exactly what to do. > That said, I really don't like the idea of hints like "use index X" > embedded in a query. I do like the idea of hints that give the planner > more information about the data. I don't have a concrete proposal, but Which is part of the problem... there's nothing to indicate we'll have support for these improved hints anytime soon, especially if a number of them depend on plan invalidation. > here's some examples of hints I'd like to see: > > "table X sometimes has millions of records and sometimes it's empty" > "Expression (table.foo = table2.bar * 2) has selectivity 0.99" > "if foo.bar = 5 then foo.field2 IS NULL" > "Column X is unique" > "function foobar() always returns either 1 or 2, and it returns 2 90% of > the time." > "if it's Monday, then table NEW_ORDERS has a cardinality of 100000, > otherwise 10." > > BTW: Do we make use of CHECK constraints in the planner? In DB2, that > was one nice and clean way of hinting the planner about things. If I > remember correctly, you could even define CHECK constraints that weren't > actually checked at run-time, but were used by the planner. I think you're right... and it is an elegant way to hint the planner. -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
