Postgresql optimalization question

Question

Hi!

I am wondering if there is any difference between adding LIMIT and OFFSET in plpgsql function body and function call.

CREATE FUNCTION test ()
RETURNS record AS
$body$
DECLARE
BEGIN
    select * from producent order by id limit 5 offset 10;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE FUNCTION test1 ()
RETURNS record AS
$body$
DECLARE
BEGIN
    select * from producent order by id;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

Which will by faster:

select * from test();

or

select * from test1() limit 5 offset 10;

Answer 1

For big tables hundred thousends of rows faster will be select * from test(); Paging inside body function eliminates many rows. Less rows earlier the better.

Answer 2

I second iddqd's answer, and additionally I'd point out that there's only one way to apply LIMIT and OFFSET to a black box function; but there may be more than one way to apply them in a query -- in principle at least. LIMIT is definitely taken into account by the optimiser (it can often avoid generating more rows than will be returned). OFFSET is, I believe, not really optimised at all and even serves as a "optimisation barrier" in more complex cases; if you have a subquery with an OFFSET clause then it will be planned separately from the main query and not collapsed into it.

Ultimately, of course, you should worry about readability and usability first: is it nicer to hide the limit/offset inside the function, or outside it in every place where the function is used?