Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 11:50 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

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

Wont this have scaling issues and issues over time as the data in the
table changes?

Suppose I make a view with the above query. With time, as the data in the
table changes, the selectivity values wont be good for planning. This may
potentially lead to a lot of changes in the view definition and other
places where this query was used.

In general, I think I step back on my point that specifying the selectivity
is a bad idea.

Could this also work (for the time being) for cross-column statistics?

Regards,

Atri

--
Regards,

Atri
*l'apprenant*