Message-ID: <452E6565.4010400@enterprisedb.com>
Date: Thu, 12 Oct 2006 16:55:17 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060915)
MIME-Version: 1.0
To: Bruce Momjian <bruce@momjian.us>
CC: "Jim C. Nasby" <jim@nasby.net>,  pgsql-performance@postgresql.org
Subject: Re: Hints proposal
References: <200610121519.k9CFJA416544@momjian.us>
In-Reply-To: <200610121519.k9CFJA416544@momjian.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

Because DB2 doesn't have MVCC, an accidental table scan is very serious, 
because with stricter isolation levels that keeps the whole table locked.

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


-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com