Message-ID: <452E6FFE.7010504@agliodbs.com>
Date: Thu, 12 Oct 2006 09:40:30 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909)
MIME-Version: 1.0
To: "Jim C. Nasby" <jim@nasby.net>
CC: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [HACKERS] Hints proposal
References: <20061012151439.GT28647@nasby.net>
 <10866.1160667752@sss.pgh.pa.us>
	<20061012162525.GF28647@nasby.net>
In-Reply-To: <20061012162525.GF28647@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jim,

>>> These hints would outright force the planner to do things a certain way.
>>> ... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */
>> This proposal seems to deliberately ignore every point that has been
>> made *against* doing things that way.  It doesn't separate the hints
>> from the queries, it doesn't focus on fixing the statistical or cost
>> misestimates that are at the heart of the issue, and it takes no account
>> of the problem of hints being obsoleted by system improvements.
>  
> Yes, but it does one key thing: allows DBAs to fix problems *NOW*. See
> also my comment below.

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.

> Yes, but as I mentioned the idea here was to come up with something that
> is (hopefully) easy to define and implement. In other words, something
> that should be doable for 8.3. Because this proposal essentially amounts
> to limiting plans the planner will consider and tweaking it's cost
> estimates, I'm hoping that it should be (relatively) easy to implement.

Even I, the chief marketing geek, am more concerned with getting a 
feature that we will still be proud of in 5 years than getting one in 
the next nine months.  Keep your pants on!

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?

I also feel that a tenet of the design of the "planner tweaks" system 
ought to be that the tweaks are collectible and analyzable in some form. 
  This would allow DBAs to mail in their tweaks to -performance or 
-hackers, and then allow us to continue improving the planner.

--Josh Berkus