From: Mitch Skinner <lists@arctur.us>
To: pgsql-performance@postgresql.org
Subject: Re: Overriding the optimizer
Date: Fri, 16 Dec 2005 11:13:31 -0800
Message-ID: <1134760411.3208.17.camel@firebolt> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@arctur.us
Subject: Re: Overriding the optimizer
In-Reply-To: <1134760411.3208.17.camel@firebolt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox