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

In response to

• Re: [HACKERS] Slow count(*) again... at 2011-02-03 00:03:06 from Greg Smith

Responses

• Re: [HACKERS] Slow count(*) again... at 2011-02-03 06:16:36 from Greg Smith

pgsql-performance by date

Next:	From: Greg Smith	Date: 2011-02-03 05:46:02
	Subject: Re: Which RAID Controllers to pick/avoid?
Previous:	From: Marcos Ortiz	Date: 2011-02-03 02:19:12
	Subject: Re: Server Configuration

pgsql-hackers by date

Next:	From: Bruce Momjian	Date: 2011-02-03 03:40:11
	Subject: compiler warning
Previous:	From: Robert Haas	Date: 2011-02-03 02:21:06
	Subject: Re: Error code for "terminating connection due to conflict with recovery"