Re: Hints proposal - Mailing list pgsql-performance

From Jim C. Nasby
Subject Re: Hints proposal
Date
Msg-id 20061012165326.GI28647@nasby.net
Whole thread Raw
In response to Re: Hints proposal  (Heikki Linnakangas <heikki@enterprisedb.com>)
List pgsql-performance
On Thu, Oct 12, 2006 at 04:55:17PM +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> >Because DB2 doesn't like hints, and the fact that they have gotten to a
> >point where they feel they do not need them, I feel we too can get to a
> >point where we don't need them either.  The question is whether we can
> >get there quickly enough for our userbase.
>
> In all fairness, when I used to work with DB2 we often had to rewrite
> queries to persuade the planner to choose a different plan. Often it was
> more of an issue of plan stability; a query would suddenly become
> horribly slow in production because a table had grown slowly to the
> point that it chose a different plan than before. Then we had to modify
> the query again, or manually set the statistics. In extreme cases we had
> to split a query to multiple parts and use temporary tables and move
> logic to the application to get a query to perform consistently and fast
> enough. I really really missed hints.

Oracle has an interesting way to deal with this, in that you can store a
plan that the optimizer generates and tell it to always use it for that
query. There's some other management tools built on top of that. I don't
know how commonly it's used, though...

Also, on the DB2 argument... I'm wondering what happens when people end
up with a query that they can't get to execute the way it should? Is the
planner *that* good that it never happens? Do you have to wait for a
fixpack when it does happen? I'm all for having a super-smart planner,
but I'm highly doubtful it will always know exactly what to do.

> That said, I really don't like the idea of hints like "use index X"
> embedded in a query. I do like the idea of hints that give the planner
> more information about the data. I don't have a concrete proposal, but

Which is part of the problem... there's nothing to indicate we'll have
support for these improved hints anytime soon, especially if a number of
them depend on plan invalidation.

> here's some examples of hints I'd like to see:
>
> "table X sometimes has millions of records and sometimes it's empty"
> "Expression (table.foo = table2.bar * 2) has selectivity 0.99"
> "if foo.bar = 5 then foo.field2 IS NULL"
> "Column X is unique"
> "function foobar() always returns either 1 or 2, and it returns 2 90% of
> the time."
> "if it's Monday, then table NEW_ORDERS has a cardinality of 100000,
> otherwise 10."
>
> BTW: Do we make use of CHECK constraints in the planner? In DB2, that
> was one nice and clean way of hinting the planner about things. If I
> remember correctly, you could even define CHECK constraints that weren't
> actually checked at run-time, but were used by the planner.

I think you're right... and it is an elegant way to hint the planner.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

pgsql-performance by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Hints proposal
Next
From: Csaba Nagy
Date:
Subject: Re: [HACKERS] Hints proposal