Thread: Re: [Fwd: Re: Functionscan estimates]

I'm not subscribed to performance at this time. I reviewed the
thread and owe everything I know about this to Wei Hong whose
brilliance exceeds all others :)  All misinterpretations are
mine alone.

I have not reviewed hellerstein's papers posted by neil, but I
will.

My understanding of this issue is at a very high user level.
In Illustra SRF functions were not necessarily special functions.
All functions could have a cost associated with them, set by the writer of
the function in order for the planner to reorder function calls.
The stonebraker airplane level example was:
    select ... from ... where f(id) = 3 and expensive_image_function(img)
The idea, of course is to weight the expensive function so it was
pushed to the end of the execution.

The only difference I see with SRFs in Postgres is that you may want
the cost represented as one row returned and another weighting representing
the number of estimated rows.  I think this conclusion has already
been drawn.

It seems to make sense, if the optimizer can use this information, to
include wild and/or educated guesses for the costs of the SRF.

I'm sure I haven't contributed here anything new, but perhaps
phrased it differently.

Copy me on replies and I'll participate as I can.

--elein

On Thu, Apr 14, 2005 at 08:36:38AM +0100, Simon Riggs wrote:
> Elein,
>
> Any chance you could join this discussion on PERFORM ?
>
> I understand you did time with Illustra. I thought they had solved the
> optimizer plug-in issue...how did they do it?
>
> Best Regards, Simon Riggs
>
>
> -------- Forwarded Message --------
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Alvaro Herrera <alvherre@dcc.uchile.cl>
> Cc: Josh Berkus <josh@agliodbs.com>, Michael Fuhr <mike@fuhr.org>,
>
> Subject: Re: [PERFORM] Functionscan estimates
> Date: Sat, 09 Apr 2005 00:00:56 -0400
> Not too many releases ago, there were several columns in pg_proc that
> were intended to support estimation of the runtime cost and number of
> result rows of set-returning functions.  I believe in fact that these
> were the remains of Joe Hellerstein's thesis on expensive-function
> evaluation, and are exactly what he was talking about here:
> http://archives.postgresql.org/pgsql-hackers/2002-06/msg00085.php
>
> But with all due respect to Joe, I think the reason that stuff got
> trimmed is that it didn't work very well.  In most cases it's
> *hard* to write an estimator for a SRF.  Let's see you produce
> one for dblink() for instance ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

On Thu, Apr 14, 2005 at 10:39:03AM -0700, elein wrote:

> All functions could have a cost associated with them, set by the writer of
> the function in order for the planner to reorder function calls.
> The stonebraker airplane level example was:
>     select ... from ... where f(id) = 3 and expensive_image_function(img)
> The idea, of course is to weight the expensive function so it was
> pushed to the end of the execution.

So there was only a constant cost associated with the function?  No
estimator function, for example?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"If you have nothing to say, maybe you need just the right tool to help you
not say it."                   (New York Times, about Microsoft PowerPoint)

Hmmm. My brain is being jostled and I'm confusing illustra-postgres,
informix-postgres and postgresql.  Some things had functions and
some things had constants and I do not remember which products had
what combination. But probably how they are in postgresql, post
hellerstein, is how I am remembering.

I can find out for sure, given a little time, by querying old contacts.
It would be best if I had a clear question to ask, though.

--elein


On Thu, Apr 14, 2005 at 02:58:09PM -0400, Alvaro Herrera wrote:
> On Thu, Apr 14, 2005 at 10:39:03AM -0700, elein wrote:
>
> > All functions could have a cost associated with them, set by the writer of
> > the function in order for the planner to reorder function calls.
> > The stonebraker airplane level example was:
> >     select ... from ... where f(id) = 3 and expensive_image_function(img)
> > The idea, of course is to weight the expensive function so it was
> > pushed to the end of the execution.
>
> So there was only a constant cost associated with the function?  No
> estimator function, for example?
>
> --
> Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
> "If you have nothing to say, maybe you need just the right tool to help you
> not say it."                   (New York Times, about Microsoft PowerPoint)
>