Date: Thu, 12 Oct 2006 11:53:27 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Bruce Momjian <bruce@momjian.us>, pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Message-ID: <20061012165326.GI28647@nasby.net>
References: <200610121519.k9CFJA416544@momjian.us>
	<452E6565.4010400@enterprisedb.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <452E6565.4010400@enterprisedb.com>
User-Agent: Mutt/1.5.11

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)