Re: [PATCHES] GUC parameter cursors_tuple_fraction

On Friday 02 May 2008 13:35:27 Simon Riggs wrote:
> On Fri, 2008-05-02 at 12:01 -0400, Tom Lane wrote:
> > "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> > > Simon Riggs wrote:
> > >> * We've said here http://www.postgresql.org/docs/faqs.TODO.html that
> > >> we "Don't want hints". If that's what we really think, then this patch
> > >> must surely be rejected because its a hint... That isn't my view. I
> > >> *now* think we do need hints of various kinds.
> > >
> > > cursors_tuple_fraction or OPTIMIZE FOR xxx ROWS isn't the kind of hints
> > > we've said "no" to in the past.
> >
> > More to the point, I think what we've generally meant by "hints" is
> > nonstandard decoration on individual SQL commands (either explicit
> > syntax or one of those interpret-some-comments kluges).
>
> Yes, that is definitely an Oracle compatibility thought.
>
> > Simon is
> > reading the policy in such a way that it would forbid all the planner
> > cost parameters, which is surely not what is intended.
>
> So we're allowed to influence the behaviour of the planner, but just not
> by touching the individual statements. OK.
>
> Can we allow a statement like
>
> SET index_weighting = '{{my_index, 0.1},{another_index, 0.5}}'
>
> That would allow us to tell a specific SQL statement that it should use
> a cost weighting of 0.1 * normal cost for the "my_index" index (etc).
> SET enable_seqscan = off; is a blunt instrument that can sometimes
> achieve the same thing, but insufficiently exact to be really useful.
> Many people use that (Sun, in their first published PostgreSQL
> benchmark...)
>
> We/I want to make the planner even better, but the above is roughly what
> people want while they're waiting for us to get the planner right.
>

I think the above would be helpful, but even then I am not sure it goes far
enough, since there might be cases where you need and index wieghted high for
a specific join within the query, but low for a different join in that query.

A further problem with this implementation would be that in general it would
require that you issue a set, run your query, and then issue another set to
put those weightings back to the defaults, which seems like an excessive
amount of overhead. As much as people like to turn their nose to in-line
query hints, the manifestation of deficiencies in the planner always
manifiest themselves at the query level, so it makes it difficult to create a
solid solution that operates somewhere else.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL