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

Shaun Thomas wrote:
> On 02/03/2011 10:38 AM, Mladen Gogala wrote:
>
>
> It all boils down to the database. Hints, whether they're
> well-intentioned or not, effectively cover up bugs in the optimizer,
> planner, or some other approach the database is using to build its
> execution.
Hints don't cover up bugs, they simply make it possible for the user to
circumvent the bugs and keep the users happy. As I hinted before, this
is actually a purist argument which was made by someone who has never
had to support a massive production database with many users for living.
> Your analogy is that PG is a gas stove, so bundle a fire
> extinguisher. Well, the devs believe that the stove should be upgraded
> to electric or possibly even induction to remove the need for the
> extinguisher.
>
In the meantime, the fire is burning. What should the hapless owner of
the database application do in the meantime? Tell the users that it will
be better in the next version? As I've said before: hints are make it or
break it point. Without hints, I cannot consider Postgres for the
mission critical projects. I am managing big databases for living and I
flatter myself that after more than two decades of doing it, I am not
too bad at it.

> If they left hints in, it would just be one more thing to deprecate as
> the original need for the hint was removed. If you really need hints
> that badly, EnterpriseDB cobbled the Oracle syntax into the planner, and
> it seems to work alright. That doesn't mean it's right, just that it
> works. EnterpriseDB will now have to support those query hints forever,
> even if the planner gets so advanced they're effectively useless.
>

I don't foresee that to happen in my lifetime. And I plan to go on for
quite a while. There will always be optimizer bugs, users will be
smarter and know more about their data than computer programs in
foreseeable future. What this attitude boils down to is that developers
don't trust their users enough to give them control of the execution
path. I profoundly disagree with that type of philosophy. DB2 also has
hints: http://tinyurl.com/48fv7w7
So does SQL Server:
http://www.sql-server-performance.com/tips/hints_general_p1.aspx
Finally, even the Postgres greatest open source competitor MySQL
supports hints: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

I must say that this purist attitude is extremely surprising to me. All
the major DB vendors support optimizer hints, yet in the Postgres
community, they are considered bad with almost religious fervor.
Postgres community is quite unique with the fatwa against hints.

--

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