Re: Function Stats WAS: Passing arguments to views

Josh Berkus wrote:
> Mark,
>
>
>>This would only seem to work for trivial functions. Most functions that
>>I write are themselves dependent on underlying tables, and without any
>>idea how many rows are in the tables, and without any idea of the
>>statistical distribution of those rows, I can't really say anything like
>>"average rows returned = 5".
>>
>>What I have wanted for some time is a function pairing system. For each
>>set returning function F() I create, I would have the option of creating
>>a statistics function S() which returns a single integer which
>>represents the guess of how many rows will be returned. S() would be
>>called by the planner, and the return value of S() would be used to
>>decide the plan. S() would need access to the table statistics
>>information. I imagine that the system would want to prevent S() from
>>running queries, and only allow it to call certain defined table
>>statistics functions and some internal math functions, thereby avoiding
>>any infinite recursion in the planner. (If S() ran any queries, those
>>queries would go yet again to the planner, and on down the infinite
>>recursion you might go.)
>>
>>Of course, some (possibly most) people could chose not to write an S()
>>for their F(), and the default of 1000 rows would continue to be used.
>>As such, this new extension to the system would be backwards compatible
>>to functions which don't have an S() defined.
>
>
> I think this is a fine idea, and I think I endorsed it the first time.
> However, even a static "function returns #" would be better than what we
> have now, and I think the S() method could take quite a bit of engineering
> to work out (for example, what if F() is being called in a JOIN or
> correlated subquery?). So I'm worried that shooting for the S() idea only
> could result in us not doing *anything* for several more versions.
>
> What I'd like to do is implement the constant method for 8.2, and work on
> doing the S() method later on. Does that make sense?
>

I have no objections to implementing the constant method sooner than the full
version. It might be useful to implement it as a subsyntax to the full version
though in preparation for later expansion. For instance, if there is a plstats
language developed, you could limit the parser for it to just functions like:

CREATE FUNCTION S() RETURNS INTEGER PROVIDES FOR F() AS $$
RETURN 5;
$$ LANGUAGE PLSTATS;

Then the language could be expanded later to allow calls to the table statistics
functions.