Re: Why we don't want hints Was: Slow count(*) again...

On Thu, Feb 3, 2011 at 8:46 PM, Josh Berkus <josh@agliodbs.com> wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed."
>
> That seems pretty straightforwards.  There are even links to prior
> discussions about what kind of system would work.  I don't think this
> text needs any adjustment; that's our clear consensus on the hint issue:
> we want a tool which works better than what we've seen in other databases.

I think it's just dumb to say we don't want hints.  We want hints, or
at least many of us do.  We just want them to actually work, and to
not suck.  Can't we just stop saying we don't want them, and say that
we do want something, but it has to be really good?

> Yes, I occasionally run across cases where having a query tweaking
> system would help me fix a pathological failure in the planner.
> However, even on data warehouses that's less than 0.1% of the queries I
> deal with, so this isn't exactly a common event.  And any hinting system
> we develop needs to address those specific cases, NOT a hypothetical
> case which can't be tested.  Otherwise we'll implement hints which
> actually don't improve queries.

No argument.

The bottom line here is that a lot of features that we don't have are
things that we don't want in the sense that we're not interested in
working on them over other things that seem more pressing, and we have
finite manpower.  But if someone feels motivated to work on it, and
can actually come up with something good, then why should we give the
impression that such a thing would be rejected out of hand?  I think
we ought to nuke that item and replace it with some items in the
optimizer section that express what we DO want, which is some better
ways of fixing queries the few queries that suck despite our best (and
very successful) efforts to produce a top-notch optimizer.

The problem with multi-column statistics is a particularly good
example of something in this class.  We may have a great solution to
that problem for PostgreSQL 11.0.  But between now and then, if you
have that problem, there is no good way to adjust the selectivity
estimates.  If this were an academic research project or just being
used for toy projects that didn't really matter, we might not care.
But this is a real database that people are relying on for their
livelihood, and we should be willing to provide a way for those people
to not get fired when they hit the 0.1% of queries that can't be fixed
using existing methods.  I don't know exactly what the right solution
is off the top of my head, but digging in our heels is not it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company