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

"Robert Haas" <robertmhaas(at)gmail(dot)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, ie
SELECT 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