Re: Index Tuning Features

"Mark Woodward" <pgsql(at)mohawksoft(dot)com> writes:

> The analyzer, at least the last time I checked, does not recognize these
> relationships.

The analyzer is imperfect but arguing from any particular imperfection is weak
because someone will just come back and say we should work on that problem --
though I note nobody's actually volunteering to do so whereas they appear to
be for hints.

I think the stronger argument is to say that there are some statistical
properties that the analyzer _cannot_ be expected to figure out. Either
because

a) they're simply too complex to ever expect to be able to find automatically,

b) too expensive to make it worthwhile in the general case, or

c) because of some operational issue such as the data changing frequently
enough that the analyzes that would be necessary to keep the statistics up
to date would become excessively expensive or even be impossible to perform
rapidly enough.

The people arguing that hints themselves are of negative benefit are taking
the argument far too far. I've never heard an Oracle DBA gripe about having to
fix hints on an upgrade; they're usually the first ones to suggest hinting a
poorly written query. In fact Oracle is going in the opposite direction of
even relying on hints internally. Its plan stability feature depends on
generating and storing hints internally associated with every query.

The argument against hints is usually that the effort would be better spent
elsewhere, not that hints are inherently a bad idea. We already have enable_*
parameters and they are absolutely necessary for testing and experimenting to
understand whether the planner is incorrect and where it has gone wrong. Hints
are just a more precisely targeted version of these. There have been plenty of
instances on this list where people posted 20-30 line query plans with several
joins of each type where the enable_* parameters were too coarse grained to
use effectively.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com