Say I have two queries that do the same thing, such as filtering something out via a complicated join, but the one queries then joins it with one table, and the other query just orders it.

Can I use a function to do the first thing (filtering out something for example) and then take the result of it to e.g. filter it more, order it, limit it or join it without any performance overhead, and if not, what are the alternatives?

Example function:

create or replace FUNCTION public.test()
  returns setof public.test
  FROM public.test

Example query deriving its data from a function:

select * from public.test() limit 10;

Would the example give me performance overhead if the table is longer than 10?

  • I bet you can see the difference in performance if you compare the execution plans. Have you tried that?
    – mustaccio
    Nov 8 '21 at 23:01
  • No i have not tried that, if i will however, i will write back Dec 19 '21 at 14:46

Normally, the performance will be worse, because the PostgreSQL optimizer has fewer options: for example, it cannot apply optimizations like a top-n sort or other things that favor a fast startup plan, because it doesn't know about the LIMIT 10 when the query is planned.

However, in this special case the performance will be just as good, because a simple SQL function like that can be inlined, that is, the optimizer replaces the function call with the function body and optimizes the resulting SQL statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.