Re: [HACKERS] Slow count(*) again... - Mailing list pgsql-performance
From | Mladen Gogala |
---|---|
Subject | Re: [HACKERS] Slow count(*) again... |
Date | |
Msg-id | 4D4A16BF.1020304@vmsinfo.com Whole thread Raw |
In response to | Re: [HACKERS] Slow count(*) again... (Greg Smith <greg@2ndquadrant.com>) |
Responses |
Re: [HACKERS] Slow count(*) again...
(Greg Smith <greg@2ndquadrant.com>)
|
List | pgsql-performance |
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
pgsql-performance by date: