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

Robert Haas <robertmhaas@gmail.com> wrote:

> I think it's just dumb to say we don't want hints.  We want hints,
> or at least many of us do.

Well, yeah.  Even those most ostensibly opposed to hints have been
known to post that they would rather not have the optimizer
recognize two logically equivalent constructs and optimize them the
same because they find the current difference "useful to coerce the
optimizer" to choose a certain plan.  That's implementing hints but
refusing to document them.  And it sometimes bites those who don't
know they're accidentally using a hint construct.  An explicit and
documented hint construct would be better.  Probably not a "use this
plan" type hint, but some form of optimization barrier hint, maybe.
You know, like OFFSET 0, but more explicitly hint-like.

> 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.

Yeah, this is probably the most important area to devise some
explicit way for a DBA who knows that such multicolumn selections
are going to be used, and is capable of calculating some correlation
factor, could supply it to the optimizer to override the naive
calculation it currently does.  Even there I would tend to think
that the sort of "do it this way" hints that people seem to
initially want wouldn't be good; it should be a way to override the
costing factor which the optimizer gets wrong, so it can do its
usual excellent job of evaluating plans with accurate costs.

> I don't know exactly what the right solution is off the top of my
> head, but digging in our heels is not it.

Well, I'm comfortable digging in my heels against doing *lame* hints
just because "it's what all the other kids are doing," which I think
is the only thing which would have satisfied the OP on this thread.
From both on-list posts and ones exchanged off-list with me, it
seems he was stubbornly resistant to properly tuning the server to
see if any problems remained, or posting particular problems to see
how they would be most effectively handled in PostgreSQL.  We
obviously can't be drawn into dumb approaches because of
ill-informed demands like that.

-Kevin