Postgresql optimalization question

Question

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;

Edmund · Accepted Answer · 2010-09-23 13:03:32Z

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?

answered Sep 23, 2010 at 13:03

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Serafin Over a year ago

If it's outside the function it's more flexible from developer point of view

baklarz2048 · Accepted Answer · 2010-09-23 12:03:45Z

0

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.

answered Sep 23, 2010 at 12:03

baklarz2048

11k2 gold badges33 silver badges38 bronze badges

Collectives™ on Stack Overflow

Postgresql optimalization question

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related