Subject: Re: Overriding the optimizer
From: Mitch Skinner <lists@arctur.us>
To: pgsql-performance@postgresql.org
In-Reply-To: <43A2251A.60907@modgraph-usa.com>
References: <200512141603.52583.blargity@gmail.com>
	<7484.1134600423@sss.pgh.pa.us> <1134629567.14248.27.camel@firebolt>
	<200512150148.16125.blargity@gmail.com>
	<1134644526.14248.60.camel@firebolt>	
	<43A1C40D.8040006@paradise.net.nz>
	<43A1F6DB.3080805@modgraph-usa.com>	
	<43A21D74.8030002@familyhealth.com.au>
	<c2d9e70e0512151800l5561fc04q7ee79593dcb4ea5c@mail.gmail.com>
	<43A2203B.2050102@familyhealth.com.au>
	<43A2251A.60907@modgraph-usa.com>
Content-Type: text/plain
Date: Fri, 16 Dec 2005 11:13:31 -0800
Message-Id: <1134760411.3208.17.camel@firebolt>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2005-12-15 at 18:23 -0800, Craig A. James wrote:
> So, "you still have no problem" is exactly wrong, because Postgres picked the wrong plan.  Postgres decided that applying myfunc() to 10,000,000 rows was a better plan than an index scan of 50,000 row_nums.  So I'm screwed.

FWIW,
The cost_functionscan procedure in costsize.c has the following comment:
        /*
         * For now, estimate function's cost at one operator eval per
function
         * call.  Someday we should revive the function cost estimate
columns in         * pg_proc...
         */

I recognize that you're trying to talk about the issue in general rather
than about this particular example.  However, the example does seem to
me to be exactly the case where the effort might be better spent
improving the optimizer (reviving the function cost estimate columns),
rather than implementing a general hinting facility.  Which one is more
effort?  I don't really know for sure, but cost_functionscan does seem
pretty straightforward.

What percentage of problems raised on this list can be fixed by setting
configuration parameters, adding indexes, increasing statistics, or
re-architecting a crazy schema?  I've only been lurking for a few
months, but it seems like a pretty large fraction.  Of the remainder,
what percentage represent actual useful feedback about what needs
improvement in the optimizer?  A pretty large fraction, I think.
Including your example.

Personally, I think whoever was arguing for selectivity hints in
-hackers recently made a pretty good point, so I'm partly on your side.
Actually, function cost "hints" don't really seem that much different
from selectivity hints, and both seem to me to be slicker solutions
(closer to the right level of abstraction) than a general hint facility.

Mitch