Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 12:57 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>
>> There's a big difference between saying to the planner, "Use plan X"
>> vs "Here's some information describing the data supporting choosing
>> plan X intelligently". The latter allows for better plans in the face
>> of varied/changing data, integrates with the planner in natural way,
>> and encourages users to understand how the planner works.
>
> +1
>
> I was thinking of varying the 'weight' of a user defined plan by an fixed
> experimental factor to tell the planner to give higher/lower preference to
> this plan, but after your idea above, I think Stephen's point of introducing
> a GUC for the factor is the only way possible and I agree with him on the
> point that eventually the user will figure out a way to force usage of his
> plan using the GUC.

GUC is not the answer beyond the "broad brush" mostly debugging level
features they already support. What do you do if your plan
simultaneously needs and does not need nestloops?

A query plan is a complicated thing that is the result of detail
analysis of the data. I bet there are less than 100 users on the
planet with the architectural knowledge of the planner to submit a
'plan'. What users do have is knowledge of the data that the database
can't effectively gather for some reason. Looking at my query above,
what it would need (assuming the planner could not be made to look
through length()) would be something like:

SELECT * FROM foo WHERE
length(bar) <= 1000 WITH SELECTIVITY 0.999
AND length(bar) >= 2 WITH SELECTIVITY 0.999;

Note, that's a trivial treatment of the syntax challenges. Ultimately
it'd probably look different and/or be hooked in a different way (say,
via the function call).

merlin