3

I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.

I have a function:

CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
    SELECT username
    FROM   users
    WHERE  users.id=user_id
$$ LANGUAGE 'sql';

I like using this function to translate from user_id to username in other queries. It's a common operation and I thought a function would speed up development time in looking up one-off stats for data analysis purposes. I am not concerned about query speeds. Is this good practice to use a function to perform this operation or should I just perform a join?

I am new to this and my coworker said it was bad practice to use functions for things that regular selects/ joins can perform. He also mentioned that functions should preferably be used for things that regular selects just can't do. So I wanted to ask other people what good practices are?

Edit: If this is a bad practice, would it be more reasonable for the case when the function does a common operation that's a little more complicated?

  • 1
    I would rather create VIEWs that do the joining for common queries – a_horse_with_no_name Jun 18 '18 at 14:48
  • Yay I have enough points to upvote you now! Good suggestion. – agent nate Jun 20 '18 at 17:18
6

A function is never going to speed up what SQL already does. In fact, if you don't actually add IMMUTABLE to it, it's likely to slow it down.

I would personally never write such a trite function.

  • If you're returning one row, I would use a correlated subquery.
  • If you're needing multiple users, I would do the join.
  • I would look probably retrieve this kind of information upon auth, like in the initial response, and send it back to the client to handle.

But above and beyond all of that, it's not merely about complexity for your function. You have a DSL that explicitly queries your schema and was created for just that purpose -- namely, SQL. What are you going to do when those functions need more logic and complexity. Been there seen that, the next step is CASE statements in functions. And, then slowly but surely you recreate a dynamic query generation mechanism. It's easier to just dynamically generate the SQL when needed.

  • Thank you for the info! I added some comments for clarity since I didn't mean speed up as in make the query run faster but speed development time for one-off calculations. – agent nate Jun 15 '18 at 21:09
  • 1
    A function still never be faster than a join. Its one more step in planning – Evan Carroll Jun 15 '18 at 21:21
  • As it's an SQL function, if it's not tagged immutable or volatile or something it can be inlined by the planner. so yeah, in many cases it's only one more planner step. – Jasen Jun 16 '18 at 1:01
  • Your leading statements about speed and volatility declaration are not right. And I can't say I agree with much of the rest, either. I added an answer. – Erwin Brandstetter Jul 1 '18 at 2:59
1

There are various scenarios where functions can speed up query performance. Most notably, PL/pgSQL functions handle SQL statements like prepared statements. To be precise it uses the SPI managers SPI_prepare:

The prepared statement also provides a place for caching an execution plan if it is found that generating a custom plan for each execution is not helpful.

The manual on PREPARE has guidelines:

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g. if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

That should also say something to your question about "a common operation that's a little more complicated".

Obviously, you can also achieve these benefits with prepared statements. But prepared statements are limited to a single DML statement. Functions can do a lot more.

But be wary of nesting functions (that cannot be inlined) in queries. Since those are planned separately (posing as optimization barrier). That might stand in the way of finding the most efficient query plan. Like in your example, if you nest the function in a query that looks up many user names at once, that's going to be a lot more expensive than a join to the lookup table.

Only declare functions IMMUTABLE that are actually immutable. Exceptions apply, like when you need to build an expression index (and know what you are doing). Example:

Else, you can actually harm performance with an incorrect volatility declaration, as it can prohibit function inlining. Quoting the Postgres Wiki on inlining:

if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator

Related:

That's merely a corrective to the currently accepted answer. But you are not primarily concerned with performance, rather with "good style". And that very much depends on the use case.

If you have a single app working with a your DB, then it's mostly a decision of language skill and taste where to encapsulate logic - in the app or in the DB. But if you have several apps working with the same DB, then it can make a lot of sense to encapsulate shared logic in the DB (with server-side functions or other means of SQL like table constraints, VIEWs, etc.) Even a trivial function like the one you display can make sense then.

  • Yes. You can use a prepared statement outside a function too. I don't see how any of this addresses the question. It's seems entirely tangential to me; but, different strokes for different folks. – Evan Carroll Jul 13 '18 at 1:29
  • I also don't think it is correct to say actually harm performance. I think you can mistakenly mark a function as immutable and get bad results because it'll get inlined when it otherwise wouldn't, but I don't think it'll "harm performance." I would like to see a test-case where IMMUTABLE actually makes performance worse. – Evan Carroll Jul 13 '18 at 1:32
  • 1
    I have turned this into a new question (and answer): dba.stackexchange.com/q/212195/3684 – Erwin Brandstetter Jul 14 '18 at 0:28

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.