To: "Jim C. Nasby" <jim@nasby.net>
cc: pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
Reply-To: pgsql-hackers@postgresql.org
Subject: Re: Hints proposal 
In-reply-to: <20061012151439.GT28647@nasby.net> 
References: <20061012151439.GT28647@nasby.net>
Comments: In-reply-to "Jim C. Nasby" <jim@nasby.net>
	message dated "Thu, 12 Oct 2006 10:14:39 -0500"
Date: Thu, 12 Oct 2006 11:42:32 -0400
Message-ID: <10866.1160667752@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

[ This is off-topic for -performance, please continue the thread in
-hackers ]

"Jim C. Nasby" <jim@nasby.net> writes:
> 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.

> It would also be useful to allow tweaking of planner cost estimates.
> This would take the general form of
> node operator value

This is at least focusing on the right sort of thing, although I still
find it completely misguided to be attaching hints like this to
individual queries.

What I would like to see is information *stored in a system catalog*
that affects the planner's cost estimates.  As an example, the DBA might
know that a particular table is touched sufficiently often that it's
likely to remain RAM-resident, in which case reducing the page fetch
cost estimates for just that table would make sense.  (BTW, this is
something the planner could in principle know, but we're unlikely to
do it anytime soon, for a number of reasons including a desire for plan
stability.)  The other general category of thing I think we need is a
way to override selectivity estimates for particular forms of WHERE
clauses.

			regards, tom lane