Re: [HACKERS] Slow count(*) again...

On 2/2/2011 7:03 PM, Greg Smith wrote:
> 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.
Greg, I understand your concerns, but let me point out two things:
1)  The basic mechanism is already there. PostgreSQL has a myriad of
ways to actually control the optimizer.  One, completely analogous to
Oracle mechanisms, is to control the cost of sequential vs. random page
scan. The other,  completely analogous to Oracle  hints, is based on the
group of switches for turning on and off various join and access
methods.  This also includes setting join_collapse limit to 1, to force
the desired join order. The third way is to actually make the optimizer
work a lot harder by setting gego_effort to 10 and
default_statistics_target to 1000 or more, which will increase the size
of histograms and increase the time and CPU spent on parsing.  I can
literally force the plan of my choosing on Postgres optimizer. The
mechanisms are already there, I am only pleading for a more elegant version.

2) The guys who may spread Postgres and help it achieve the desired
world domination, discussed here the other day, are database
administrators in the big companies. If you get people from JP Morgan
Chase, Bank of America, Goldman Sachs or Lehman Brothers to start using
Postgres for serious projects, the rest will follow the suit.  People
from some of these companies have already been seen on NYC Postgres
meetings.
Granted, MySQL started on the other end of the spectrum, by being used
for ordering downloaded MP3 collections, but it had found its way into
the corporate server rooms, too. The techies at big companies are the
guys who will or will not make it happen. And these guys are not
beginners.  Appeasing them may actually go a long way.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com