11

Beginner question:

I have an expensive function f(x, y) on two columns x and y in my database table.

I want to execute a query that gives me the result of the function as a column and puts a constraint on it, something like

SELECT *, f(x, y) AS func FROM table_name WHERE func < 10;

However this doesn't work, so I will have to write something like

SELECT *, f(x, y) AS func FROM table_name WHERE f(x, y) < 10;

Will this run the expensive function twice? What's the best way to do this?

  • 1
    Is the function STABLE/IMMUTABLE or VOLATILE? – Evan Carroll Jan 25 '18 at 15:44
22

Let's create a function that has a side effect so that we can see how many times it is executed:

CREATE OR REPLACE FUNCTION test.this_here(val integer)
    RETURNS numeric
    LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE WARNING 'I am called with %', val;
    RETURN sqrt(val);
END;
$function$;

And then call this like you do:

SELECT this_here(i) FROM generate_series(1,10) AS t(i) WHERE this_here(i) < 2;

WARNING:  I am called with 1
WARNING:  I am called with 1
WARNING:  I am called with 2
WARNING:  I am called with 2
WARNING:  I am called with 3
WARNING:  I am called with 3
WARNING:  I am called with 4
WARNING:  I am called with 5
WARNING:  I am called with 6
WARNING:  I am called with 7
WARNING:  I am called with 8
WARNING:  I am called with 9
WARNING:  I am called with 10
    this_here     
──────────────────
                1
  1.4142135623731
 1.73205080756888
(3 rows)

As you see, the function is called at least once (from the WHERE clause), and when the condition is true, once again to produce the output.

To avoid the second execution, you can do what Edgar suggests - namely wrap the query and filter the result set:

SELECT * 
  FROM (SELECT this_here(i) AS val FROM generate_series(1,10) AS t(i)) x 
 WHERE x.val < 2;

WARNING:  I am called with 1
... every value only once ...
WARNING:  I am called with 10

To further check how this works, one can go to pg_stat_user_functions and check calls there (given track_functions is set to 'all).

Let's try with something that has no side effect:

CREATE OR REPLACE FUNCTION test.simple(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT sqrt(val);
$function$;

SELECT simple(i) AS v 
  FROM generate_series(1,10) AS t(i)
 WHERE simple(i) < 2;
-- output omitted

SELECT * FROM pg_stat_user_functions WHERE funcname = 'simple';
-- 0 rows

simple() is actually too simple so it can be inlined, therefore it does not appear in the view. Let's make it un-inlinable:

CREATE OR REPLACE FUNCTION test.other_one(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT 1; -- to prevent inlining
SELECT sqrt(val);
$function$;

SELECT other_one(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE other_one(i) < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time 
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    13 │      0.218 │     0.218

SELECT *
  FROM (SELECT other_one(i) AS v FROM generate_series(1,10) AS t(i)) x 
 WHERE v < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time 
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    23 │      0.293 │     0.293

As it looks, the picture is the same with or without side effects.

Changing other_one() to IMMUTABLE changes the behaviour (maybe surprisingly) to the worse, as it will be called 13 times in both queries.

  • Could the decision to call the function again be determined by the presence of a side-effecting instruction in the function's body? Is it possible to find out whether a function with the same parameter(s) is called once or many times per row by looking at the query plan (if, for instance, it had no side-effecting part)? – Andriy M Jan 25 '18 at 14:30
  • @AndriyM I can imagine yes, but currently have no time to play with a debugger to see what is actually called. Will add a bit about inlined functions (which is not the case the OP should expect, as it sounds). – dezso Jan 25 '18 at 14:32
  • 1
    @AndriyM, according to: postgresql.org/docs/9.1/static/sql-createfunction.html a function is assumed to be VOLATILE if not declared as IMMUTABLE or STABLE. VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. – Lennart Jan 25 '18 at 15:24
5

Try to call it again:

SELECT
     *
FROM (
SELECT
     *,
     f(x, y) AS func
FROM table_name
) a
WHERE a.func < 10;

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.