SQL function very slow compared to query without function wrapper

Question

I have this PostgreSQL 9.4 query that runs very fast (~12ms):

SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email, 
  customers.name,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = 2
ORDER BY
  auth_web_events.id DESC;

But if I embed it into a function, the query runs very slow through all data, seems that is running through every record, what am I missing?, I have ~1M of data and I want to simplify my database layer storing the large queries into functions and views.

CREATE OR REPLACE FUNCTION get_web_events_by_userid(int) RETURNS TABLE(
    id int,
    time_stamp timestamp with time zone,
    description text,
    origin text,
    userlogin text,
    customer text,
    client_ip inet
     ) AS
$func$
SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email AS user, 
  customers.name AS customer,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = $1
ORDER BY
  auth_web_events.id DESC;
  $func$ LANGUAGE SQL;

The query plan is:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=61.905..61.906 rows=2 loops=1)"
"  Sort Key: auth_web_events.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=61.884..61.893 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=61.874..61.879 rows=2 loops=1)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events  (cost=0.57..4.58 rows=1 width=61) (actual time=61.860..61.860 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"              ->  Index Scan using auth_user_pkey on auth_user  (cost=0.14..8.16 rows=1 width=524) (actual time=0.005..0.005 rows=1 loops=2)"
"                    Index Cond: (id = 2)"
"        ->  Index Scan using customers_id_idx on customers  (cost=0.14..8.16 rows=1 width=222) (actual time=0.004..0.005 rows=1 loops=2)"
"              Index Cond: (id = auth_user.customer_id_fk)"
"Planning time: 0.369 ms"
"Execution time: 61.965 ms"

I'm calling the funcion on this way:

SELECT * from get_web_events_by_userid(2)

The query plan for the function:

"Function Scan on get_web_events_by_userid  (cost=0.25..10.25 rows=1000 width=172) (actual time=279107.142..279107.144 rows=2 loops=1)"
"Planning time: 0.038 ms"
"Execution time: 279107.175 ms"

EDIT: I just change the parameters, and the issue persist.
EDIT2: Query plan for the Erwin answer:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=0.048..0.049 rows=2 loops=1)"
"  Sort Key: w.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=0.030..0.037 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=0.023..0.025 rows=2 loops=1)"
"              ->  Index Scan using auth_user_pkey on auth_user u  (cost=0.14..8.16 rows=1 width=524) (actual time=0.011..0.012 rows=1 loops=1)"
"                    Index Cond: (id = 2)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events w  (cost=0.57..4.58 rows=1 width=61) (actual time=0.008..0.008 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"        ->  Index Scan using customers_id_idx on customers c  (cost=0.14..8.16 rows=1 width=222) (actual time=0.003..0.004 rows=1 loops=2)"
"              Index Cond: (id = u.customer_id_fk)"
"Planning time: 0.541 ms"
"Execution time: 0.101 ms"

What's the query plan on the first one? Does it use an index? — jpmc26, Jan 30 '15 at 01:28
@jpmc26: I disagree with your advice. Putting large queries in functions can be extremely useful if *done right*. It's often much more convenient to maintain the function inside the database, where it's easier to track dependencies. It's typically *faster* this way. The application does not have to prepare complex queries for every session - among other things sending a long query string instead of just a simple function call. The best course of action depends on the complete picture. — Erwin Brandstetter, Jan 30 '15 at 01:46
Your version of Postgres? Is the plain SQL using the same query plan? — Erwin Brandstetter, Jan 30 '15 at 01:58
@ErwinBrandstetter I suppose I'm coming from the perspective where for most applications, a small savings in speed at the cost of the increased complexity is really not worth it. Unless you're dealing with something that has huge performance demands, developer time is one of the biggest costs. Additionally, this makes restoration after deployment failure more difficult, since the database data must be preserved unlike app files. (Exceptions include bureaucratic dystopias.) So putting the query in code is a sensible default, and you should have a specific reason to do otherwise. — jpmc26, Jan 30 '15 at 01:59
@jpmc26: You keep claiming "increased complexity", where I see potential for reduced complexity. The app does not have to prepare (or worse, concatenate) the query, just call the stored procedure. Your favorite tag is python and your arguments reflect the skill. My primary expertise is with Postgres and I have a different perspective. You are generalizing claims based on your perspective, not on actual requirements of the (unknown) use case. It's a common pattern. — Erwin Brandstetter, Jan 30 '15 at 02:12
@ErwinBrandstetter I'm sorry for being unclear. I'm not disputing what you've said about the complexity of what happens at runtime. I'm talking about the complexity of developing and maintaining an application. All I I mean is that a database function is one more piece that can bite you and cost more development time (as it has in the OP's case) and that DB changes are more difficult to handle well during deployment. I completely agree there are cases where functions make sense, but the OP's description suggests they're doing it more as a "best practice," without having weighed the drawbacks. — jpmc26, Jan 30 '15 at 02:35
That's odd. I don't see a reason why the function should use a different query plan. Nothing obviously wrong with it. How did you test exactly? I am out of time now, sorry. — Erwin Brandstetter, Jan 30 '15 at 02:39
@jpmc26: It's a huge topic with many things to consider. Skill is a big factor. You last comment seems mostly agreeably, even from a different perspective. — Erwin Brandstetter, Jan 30 '15 at 02:45
can you give us an example of how you're calling the function? — Joe Love, Jan 30 '15 at 04:16
I was thinking that's better put the big sql code into the database, if you can do that with postgresql why not? on this way my app can gain a greater performance and my app code more neat, I'm migrating from mongodb and mysql, and I'm trying to benefit of postgresql great features up to the maximum. — Mmeyer, Jan 30 '15 at 12:34
IMO, there is a difference in the way that parameters are handled. The function does not know about the *actual* parameter(s), so it cannot use statistics to generate a plan. — joop, Jan 30 '15 at 13:04
@ErwinBrandstetter sorry, I'm just paste the function query plan in my question. — Mmeyer, Jan 30 '15 at 14:10
Perhaps some possible overlap with http://stackoverflow.com/questions/9305133/why-does-postgresql-treat-my-query-differently-in-a-function with the exception that you have already defined the function as `LANGUAGE sql`. But at least the discussion in that question might help explain why the query runs differently inside and outside a function context. — rchang, Jan 30 '15 at 14:18
@rchang On the contrary, it's even more confusing. The answer there states that as of 9.2, queries in functions *should* be replanned based on input values. So you would expect them to use the same plan. — jpmc26, Jan 30 '15 at 14:32
Strange. the raw query has a row estimation of `rows=1` while the function has a row estimation of `rows=1000`, so the plans definitely aren't the same (even though we can't actually see what the plan when it's running inside the function is). This is going to sound weird, but what happens if you just do `SELECT get_web_events_by_userid(2)` and `SELECT * FROM (SELECT get_web_events_by_userid(2)) x`? — jpmc26, Jan 30 '15 at 14:36
One other thing to check, is `auth_web_events.user_id_fk` actually an `INT` column? (Sounds strange, I know, but worth making sure.) — jpmc26, Jan 30 '15 at 14:45
And why restrict by `auth_web_events.user_id_fk = $1` instead of by ` auth_user.id = $1` ? (this happens "almost naturally" if you rewrite into JOIN-syntax) — joop, Jan 30 '15 at 15:21
**This makes no sense.** An SQL function would use indexes just like the raw query, unless your installation is seriously misconfigured or broken. A type mismatch might explain it (like @jpmc26 commented). Did you make sure that both invocations are working with the same *same database*? [Are you sure you are calling the right function?](http://stackoverflow.com/questions/15836218/where-i-can-find-my-plpython-functions-in-postgres/15841124#15841124) What do you get if you run this? (for debugging only) `SET enable_seqscan=f; SELECT * from get_web_events_by_userid(2);` — Erwin Brandstetter, Jan 30 '15 at 16:50
Concerning a possible type mismatch: Table definitions (exact, complete `CREATE TABLE` statements, or what you get from `\d tbl` in psql) are *always* helpful for performance questions. — Erwin Brandstetter, Jan 30 '15 at 16:52

Erwin Brandstetter · Accepted Answer · 2023-07-17T22:10:58.543

10

~~user~~

While rewriting your function I realized that you added column aliases here:

SELECT 
  ...
  auth_user.email AS user, 
  customers.name AS customer,

.. which wouldn't do anything to begin with, since those aliases are invisible outside the function and not referenced inside the function. So they would be ignored. For documentation purposes better use a comment.

But it also makes your query invalid, because user is a completely reserved word and cannot be used as column alias unless double-quoted.

Oddly, in my tests the function seems to work with the invalid alias. Probably because it is ignored (?). But I am not sure this couldn't have side effects.

Your function rewritten (otherwise equivalent):

CREATE OR REPLACE FUNCTION get_web_events_by_userid(int)
  RETURNS TABLE (
     id int
   , time_stamp timestamptz
   , description text
   , origin text
   , userlogin text
   , customer text
   , client_ip inet
  )
  LANGUAGE sql STABLE AS
$func$
SELECT w.id
     , w.time_stamp
     , w.description 
     , w.origin  
     , u.email     -- AS user   -- make this a comment!
     , c.name      -- AS customer
     , w.client_ip
FROM   public.auth_user       u
JOIN   public.auth_web_events w ON w.user_id_fk = u.id
JOIN   public.customers       c ON c.id = u.customer_id_fk 
WHERE  u.id = $1   -- reverted the logic here
ORDER  BY w.id DESC
$func$;

Obviously, the STABLE keyword changed the outcome. Function volatility should not be an issue in the test situation you describe. The setting does not normally profit a single, isolated function call. Read details in the manual. Also, standard EXPLAIN does not show query plans for what's going on inside functions. You could employ the additional module auto-explain for that:

Get query plan for SQL statement nested in a PL/pgSQL function

You have a very odd data distribution:

auth_web_events table has 100000000 records, auth_user->2 records, customers-> 1 record

Since you didn't define otherwise, the function assumes an estimate of 1000 rows to be returned. But your function is actually returning only 2 rows. If all your calls only return (in the vicinity of) 2 rows, just declare that with an added ROWS 2. Might change the query plan for the VOLATILE variant as well (even if STABLE is the right choice anyway here).

edited Jul 17 '23 at 22:10

answered Jan 30 '15 at 17:41

Erwin Brandstetter

605,456
145
1,078
1,228

Seems the issue persist: "Function Scan on get_web_events_by_userid (cost=0.25..10.25 rows=1000 width=172) (actual time=250263.587..250263.587 rows=2 loops=1)" "Planning time: 0.036 ms" "Execution time: 250263.612 ms" auth_web_events table has 100000000 records, auth_user->2 records, customers-> 1 record – Mmeyer Jan 30 '15 at 18:00
@Mmeyer: Pity! Then please consider the advice for further debugging in the latest comments to the question ... and verify that both calls (func & query) return the identical results. – Erwin Brandstetter Jan 30 '15 at 18:03
thanks!, with the last update in your answer the function is running ok, I just pasted the query plan in my question, what was the problem? – Mmeyer Jan 30 '15 at 18:24
@Mmeyer: Not sure. You tell me. You can start with your original function and update bit by bit to see which of my updates did the trick: 1. remove illegal alias. 2. rewrite with explicit join syntax (shouldn't make a diff). 3. Add `STABLE` (instead of default `VOLATILE`). 4. Change WHERE condition to `u.id = $1`. 1 & **4** are the most likely candidates. – Erwin Brandstetter Jan 30 '15 at 18:34
the STABLE statement did the trick, I don't understand actually what means. – Mmeyer Jan 30 '15 at 18:40
1

@Mmeyer: Nice that it works. `STABLE` is the correct setting here and may benefit repeated calls in the context of a bigger query. But it shouldn't make the difference for your isolated test case. I added a bit to the answer. – Erwin Brandstetter Jan 30 '15 at 19:24
Something that I find odd is the execution plan. With the original version, it merely stated it was going to evaluate a function. With your updated definition, the presented plan includes details of how the query inside the function is evaluated. Any idea why? Could the reason it doesn't present a more detailed plan be that the planner isn't optimizing the query inside the function? – jpmc26 Jan 30 '15 at 20:10
@jpmc26: That's probably just a misleading presentation of the OP. The plan is for the query itself. Standard `EXPLAIN` does not show query plans for what's going on *inside* functions. You would need to use auto-explain for that. I'll add a note to my answer. – Erwin Brandstetter Jan 30 '15 at 20:31
1

@ErwinBrandstetter Hm. After I posted my comment, I was able to reproduce that `EXPLAIN` behavior with a very simple function, by switching between `VOLATILE` and `STABLE`. I did so in psql, and `SHOW` on a few of `auto_explain`'s config parameters gave "unrecognized" errors. So I'm pretty sure `auto_explain` wasn't loaded or enabled. I'm on 9.3. I can post a question if it's interesting at all. – jpmc26 Jan 30 '15 at 21:04
1

Something else curious: I get `rows=1000` (same as the OP) when it doesn't show the function's inside plan. My function can return a maximum of 5 rows, and it correctly estimates 5 rows when it does show the inner plan. I'm also seeing that the runtime goes up by a factor of around 3 times from the `EXPLAIN ANALYZE` output. (The query is so fast, I'm not sure that's significant, though.) Can the planner ever actually forego the function call and just inline the query as a subquery? That would explain a lot. – jpmc26 Jan 30 '15 at 21:10
@jpmc26: Sounds interesting, may be worth a question. I am off for the weekend, though. – Erwin Brandstetter Jan 30 '15 at 21:15
2

@ErwinBrandstetter just an FYI, but I found this question and answer while researching a very similar problem. I had a query that was running in about 91ms, and when I put it in a function, it jumped to over 4,900ms. Adding `STABLE` made it perform similar to the raw SQL. – David S May 10 '15 at 20:41

pwnyexpress · Answer 2 · 2015-01-30T14:59:47.033

0

You will get better performance by making this query dynamic and using plpgsql.

CREATE OR REPLACE FUNCTION get_web_events_by_userid(uid int) RETURNS TABLE(
    id int,
    time_stamp timestamp with time zone,
    description text,
    origin text,
    userlogin text,
    customer text,
    client_ip inet
     ) AS $$
BEGIN

RETURN QUERY EXECUTE
'SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email AS user, 
  customers.name AS customer,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = ' || uid ||
'ORDER BY
  auth_web_events.id DESC;'

END;
$$ LANGUAGE plpgsql;

edited Jan 30 '15 at 14:59

answered Jan 30 '15 at 14:36

pwnyexpress

1,016
7
14

Um, does that actually `RETURN` anything? Wouldn't you have to use `RETURN QUERY`? – jpmc26 Jan 30 '15 at 14:37
I imagine that might affect your results. I don't know if it can optimize the query execution away, but it certainly seems like you should re-verify that the performance is still better. – jpmc26 Jan 30 '15 at 14:41
I getting an : ERROR: syntax error at or near "SELECT" LINE 13: SELECT ^ ********** Error ********** ERROR: syntax error at or near "SELECT" SQL state: 42601 Character: 268 – Mmeyer Jan 30 '15 at 14:47
See my edit. Forgot that the query expression needs to be a string. – pwnyexpress Jan 30 '15 at 15:00
@jpmc26 Because the query is dynamic it will generate a new plan for each execution instead of generating a general one. This should result in better performance. – pwnyexpress Jan 30 '15 at 15:05
1

@pwnyexpress See [this question & answer](http://stackoverflow.com/a/9305273/1394393), as rchang pointed out. The query should get replanned regardless of whether it's in a function of any kind or not, as of 9.2 and up. – jpmc26 Jan 30 '15 at 16:20
3

That should not be necessary at all. A simple SQL function should be all you need here. PL/pgSQL might be useful without dynamic SQL, because it treats the query like a prepared statement (reuses the query plan), but that's completely unrelated to the issue at hand. In short: this answer is misleading and basically wrong. Also, when using dynamic SQL, it's much better to pass value parameters with a `USING` clause instead of concatenating text representations. – Erwin Brandstetter Jan 30 '15 at 16:54

SQL function very slow compared to query without function wrapper

2 Answers2

Linked