"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