Re: [HACKERS] Slow count(*) again...
От | Mladen Gogala |
---|---|
Тема | Re: [HACKERS] Slow count(*) again... |
Дата | |
Msg-id | 4D49D3D6.2010002@vmsinfo.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] Slow count(*) again... (Kenneth Marshall <ktm@rice.edu>) |
Список | pgsql-performance |
Kenneth Marshall wrote: > > > I see them come up regularly. However, there really are not all that > many when you consider how many people are using PostgreSQL. Its > optimizer works quite well. Knowing how hints can be misused, I would > rather have the developers use their resource to improve the optimizer > than spend time on a hint system that would be mis-used over and over > by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/... > groups. I certainly have had a fun time or two in my limited Oracle > experience tracking down a hint-based performance problem, so it > works both ways. > > Regards, > Ken > Ken, the story is really simple: when a problem with a bad query arises, the DBA has to make it work, one way or another. The weapon of choice are usually hints, but there is also the ability to set the critical statistic variables to the desired values. If my users are screaming that the application response time is slow, I cannot afford to wait for developers to fix the optimizer. I will therefore not use Postgres for my mission critical applications, as long as there are no hints. Oracle is expensive, but not as expensive as the downtime. And that's the bottom line. Yes, hints can cause problems, but the absence of hints and wait interface can cause even bigger problems. This is not a choice between good and evil, as in the Nick Cage movies, it is a choice between evil and lesser evil. I would love to be able to use Postgres for some of my mission critical applications. Saving tens of thousands of dollars would make me a company hero and earn me a hefty bonus, so I have a personal incentive to do so. Performance is normally not a problem. If the application is carefully crafted and designed, it will work more or less the same as Oracle. However, applications sometimes need maintenance. Ruth from sales wants the IT to start ingesting data in UTF8 because we have clients in other countries. She also wants us to track language and countries. Columns have to be added to the tables, applications have to be changed, foreign keys added, triggers altered, etc, etc. What you end up with is usually less than optimal. Applications have life cycle and they move from being young and sexy to being an old fart application, just as people do. Hints are Viagra for applications. Under the ideal conditions, it is not needed, but once the app is past certain age.... The other problem is that plans change with the stats, not necessarily for the better. People clean a large table, Postgres runs auto-vacuum, stats change and all the plans change, too. If some of the new plans are unacceptable, there isn't much you can do about it, but to hint it to the proper plan. Let's not pretend, Postgres does support sort of hints with the "set enable_<access method>" and random/sequential scan cost. Also, effective cache size is openly used to trick the optimizer into believing that there is more memory than there actually is. Hints are already there, they're just not as elegant as Oracle's solution. If I set sequential page cost to 4 and random page cost to 1, I have, effectively, introduced rule based optimizer to Postgres. I am not sure why is there such a puritanical resistance to hints on one side and, on other side, there are means to achieve exactly the same thing. As my signature line says, I am a senior Oracle DBA, with quite a bit of experience. What I need to approve moving mission critical applications to Postgres are better monitoring tools and something to help me with quick and dirty fixes when necessary. I am willing to learn, I got the company to invest some money and do pilot projects, but I am not prepared to have my boss saying "we could have fixed the problem, had we stayed on Oracle". BTW: On my last airplane trip, I saw Nick Cage in the "Sorcerer's Apprentice" and my brain still hurts. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
В списке pgsql-performance по дате отправления: