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

Mladen Gogala wrote:
> With all due respect, I don't see how does the issue of hints fall 
> into this category? As I explained, the mechanisms are already there, 
> they're just not elegant enough.

You're making some assumptions about what a more elegant mechanism would 
look to develop that are simplifying the actual situation here.  If you 
take a survey of everyone who ever works on this area of the code, and 
responses to this thread are already approaching a significant 
percentage of such people, you'll discover that doing what you want is 
more difficult--and very much "not elegant enough" from the perspective 
of the code involved--than you think it would be.

It's actually kind of funny...I've run into more than one person who 
charged into the PostgreSQL source code with the goal of "I'm going to 
add good hinting!"  But it seems like the minute anyone gets enough 
understanding of how it fits together to actually do that, they realize 
there are just plain better things to be done in there instead.  I used 
to be in the same situation you're in--thinking that all it would take 
is a better UI for tweaking the existing parameters.  But now that I've 
actually done such tweaking for long enough to get a feel for what's 
really wrong with the underlying assumptions, I can name 3 better uses 
of development resources that I'd rather work on instead.  I mentioned 
incorporating cache visibility already, Robert has talked about 
improvements to the sensitivity estimates, and the third one is 
improving pooling of work_mem so individual clients can get more of it 
safely.

> Well, those two databases are also used much more widely than 
> Postgres, which means that they're doing something better than Postgres.

"Starting earlier" is the only "better" here.  Obviously Oracle got a 
much earlier start than either open-source database.  The real 
divergence in MySQL adoption relative to PostgreSQL was when they 
released a Windows port in January of 1998.  PostgreSQL didn't really 
match that with a fully native port until January of 2005.

Check out 
http://www.indeed.com/jobtrends?q=postgres%2C+mysql%2C+oracle&relative=1&relative=1 
if you want to see the real story here.  Oracle has a large installed 
base, but it's considered a troublesome legacy product being replaced 
whenever possible now in every place I visit.  Obviously my view of the 
world as seen through my client feedback is skewed a bit toward 
PostgreSQL adoption.  But you would be hard pressed to support any view 
that suggests Oracle usage is anything other than flat or decreasing at 
this point.  When usage of one product is growing at an expontential 
rate and the other is not growing at all, eventually the market share 
curves always cross too.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books