Re: WIP patch: convert SQL-language functions to return tuplestores

"Robert Haas" <robertmhaas@gmail.com> writes:
>>> I thought that the bad case for a tuplestore was if the set returning
>>> function was expensive and the user used it with a LIMIT clause. In the
>>> tuplestore case you evaluate everything then throw it away.
>> 
>> I'm not terribly excited by that example --- but in any case, the real
>> solution to any problem that involves communication between function and
>> calling query is to make sure that the function can get inlined into the
>> query.  That was an option we didn't have back in 8.2; but it's there
>> now.  My test case deliberately disables that optimization ...

> I'm pretty excited by that example.  LIMIT/OFFSET is really useful as
> a way of paginating query results for display on a web page (show
> results 1-100, 101-200, etc), and I use it on potentially expensive
> SRFs just as I do on tables and views.

I suspect it doesn't help you as much as you think.  It's always been
the case that SRFs in FROM-items are fed through a tuplestore, and so
are plpgsql SRF results.  The only place where you could win with an
outside-the-function LIMIT in existing releases is if (1) it's a
SQL-language function and (2) you call it in the SELECT targetlist, ieSELECT foo(...) LIMIT n;

It seems to me that if you have a situation where you are really
depending on the performance of such a construct, you could push the
LIMIT into the function:
CREATE FUNCTION foo(..., n bigint) RETURNS SETOF whatever AS $$    SELECT ... LIMIT $something$$ LANGUAGE sql;

This would likely actually give you *better* performance since the plan
for the function's SELECT would be generated with awareness that it was
going to be LIMIT'ed.

So my feeling is that people are obsessing about a corner case and
losing sight of the fact that this patch appears to be a performance
boost in more-typical cases ... not to mention the new features it
enables.
        regards, tom lane