Re: [PERFORM] Hints proposal

Jim,

> > I don't see how adding extra tags to queries is easier to implement
> > than an ability to modify the system catalogs.  Quite the opposite,
> > really.
> >
> > And, as I said, if you're going to push for a feature that will be
> > obsolesced in one version, then you're going to have a really rocky
> > row to hoe.
>
> Unless you've got a time machine or a team of coders in your back
> pocket, I don't see how the planner will suddenly become perfect in
> 8.4...

Since you're not a core code contributor, I really don't see why you
continue to claim that query hints are going to be easier to implement
than relation-level statistics modification.  You think it's easier, but
the people who actually work on the planner don't believe that it is.

> We've been seeing the same kinds of problems that are very difficult (or
> impossible) to fix cropping up for literally years... it'd be really
> good to at least be able to force the planner to do the sane thing even
> if we don't have the manpower to fix it right now...

As I've said to other people on this thread, you keep making the incorrect
assumption that Oracle-style query hints are the only possible way of
manual nuts-and-bolts query tuning.  They are not.

> > I actually think the way to attack this issue is to discuss the kinds
> > of errors the planner makes, and what tweaks we could do to correct
> > them. Here's the ones I'm aware of:
> >
> > -- Incorrect selectivity of WHERE clause
> > -- Incorrect selectivity of JOIN
> > -- Wrong estimate of rows returned from SRF
> > -- Incorrect cost estimate for index use
> >
> > Can you think of any others?
>
> There's a range of correlations where the planner will incorrectly
> choose a seqscan over an indexscan.

Please list some if you have ones which don't fall into one of the four
problems above.

> Function problems aren't limited to SRFs... we have 0 statistics ability
> for functions.
>
> There's the whole issue of multi-column statistics.

Sure, but again that falls into the category of "incorrect selectivity for
WHERE/JOIN".  Don't make things more complicated than they need to be.

> Well, one nice thing about the per-query method is you can post before
> and after EXPLAIN ANALYZE along with the hints.

One bad thing is that application designers will tend to use the hint, fix
the immediate issue, and never report a problem at all.  And query hints
would not be collectable in any organized way except the query log, which
would then require very sophisticated text parsing to get any useful
information at all.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco