Re: Why we don't want hints

On Sun, Feb 13, 2011 at 3:29 PM, Josh Berkus <josh@agliodbs.com> wrote:
> I've wordsmithed Chris's changes some, and then spun off a completely
> separate page for Hints discussion, since the NotToDo item was becoming
> too long.
>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>
> Please, NO!
>
> This is exactly the kind of hint that I regard as a last resort if we
> run out of implementation alternatives.  Any hint which gets coded into
> the actual queries becomes a *massive* maintenance and upgrade headache
> thereafter.  If we're implementing a hint alternative, we should look at
> stuff in this priority order:
>
> 1. Useful tuning of additional cost parameters by GUC (i.e.
> cursor_tuple_fraction)
> 2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500")
> 3. Adding new parameters to modify on database objects (i.e.
> "distribution=normal(1.5,17)","new_rows=0.1")
> 4. Query hints (if all of the above fails to give fixes for some tested
> problem)

I fail to see how 1 through 3 can tell the planner the correlation
between two fields in two separate tables.