Mladen Gogala wrote:
Greg, how many questions about queries not using an index have you seen? There is a reason why people keep asking that. The sheer number of questions like that on this group should tell you that there is a problem there. There must be a relatively simple way of influencing optimizer decisions.
I think that's not quite the right question. For every person like yourself who is making an informed "the optimizer is really picking the wrong index" request, I think there are more who are asking for that but are not actually right that it will help. I think you would agree that this area is hard to understand, and easy to make mistakes about, yes? So the right question is "how many questions about queries not using an index would have actually benefitted from the behavior they asked for?" That's a much fuzzier and harder to answer question.
I agree that it would be nice to provide a UI for the informed. Unfortunately, the problem I was pointing out is that doing so could, on average, make PostgreSQL appear to run worse to people who use it. Things like which index and merge type are appropriate changes as data comes in, and some of the plan switches that occur because of that are the right thing to do--not a mistake on the optimizer's part. I'm sure you've seen people put together plan rules for the RBO that worked fine on small data sets, but were very wrong as production data volume went up. That problem should be less likely to happen to a CBO approach. It isn't always, of course, but trying to build a RBO-style approach from scratch now to resolve those cases isn't necessarily the right way to proceed.
Given limited resources as a development community, it's hard to justify working on hinting--which has its own complexity to do right--when there are so many things that I think are more likely to help *everyone* that could be done instead. The unfortunate situation we're in, unlike Oracle, is that there isn't a practically infinite amount of money available to fund every possible approach here, then see which turn out to work later after our customers suffer through the bad ones for a while.
With all due respect, I consider myself smarter than the optimizer. I'm 6'4", 235LBS so telling me that you disagree and that I am more stupid than a computer program, would not be a smart thing to do. Please, do not misunderestimate me.
I remember when I used to only weigh that much. You are lucky to be such a slim little guy!
Oh, I guess I should add, :) -- Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
