LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

Question

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the function is called for every row.

Is there a way to rewrite it so that it is only called for rows where t1.type is equal to 5? The below still calls the function for every row, although I have a condition set.

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;

Test case:

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    type INTEGER,
    col1 INTEGER,
    col2 INTEGER
);
-- Sample data for table1
INSERT INTO table1 (name, type, col1, col2)
VALUES
    ('Row 1', 5, 10, 20),
    ('Row 2', 7, 15, 25),
    ('Row 3', 10, 30, 40);
CREATE OR REPLACE FUNCTION function_name(col1_arg INTEGER, col2_arg INTEGER)
RETURNS TABLE (col1 INTEGER, col2 INTEGER)
AS $$
BEGIN
    RAISE LOG 'Function called';
    RETURN QUERY SELECT col1_arg * 2 AS col1, col2_arg * 3 AS col2;
END;
$$ LANGUAGE plpgsql;
-- Test Query
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;

Akina · Accepted Answer · 2024-04-12T11:00:44.710

SELECT t0.name, t0.type, t2.col1, t2.col2
FROM table1 t0
LEFT JOIN table1 t1 ON t0.id = t1.id AND t1.type = 5
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.id IS NOT NULL;

fiddle

When I run your code on my PostgreSQL server it calls the function every time. – Björn Morén

Test something similar to:

WITH 
cte AS (
    SELECT * FROM table1 WHERE type = 5
    )
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM cte t1
CROSS JOIN LATERAL function_name(t1.col1, t1.col2) AS t2
UNION ALL 
SELECT name, type, NULL, NULL
FROM table1 
WHERE type <> 5 -- OR type IS NULL
;

score 1 · Answer 2 · answered Apr 12 '24 at 08:46

Attempt #1: putting your condition in the WHERE clause works but it changes the query results as it will remove all rows with type!=5:

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON (true) 
WHERE t1.type=5;

...however you can put the rows back in:

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON (true) 
WHERE t1.type=5
UNION ALL
SELECT t1.name, t1.type, NULL, NULL
FROM table1 t1
WHERE t1.type!=5

If "table1" isn't a table but a complicated query, you can put it in a materialized CTE to avoid doing it twice.

Attempt #2: add "RETURNS NULL ON NULL INPUT" to the function and call it with nulls when you don't actually want to call it, which won't execute the function, which is much faster:

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL (SELECT t1.col1,t1.col2 WHERE t1.type = 5) tt ON (true)
LEFT JOIN LATERAL function_name(tt.col1, tt.col2) t2 ON (true);

(you could also use CASE to set all parameters of the function to NULL when you don't want to call it).

Attempt #3

SELECT t1.name, t1.type, (f).*
FROM table1 t1
LEFT JOIN LATERAL (
  SELECT function_name(tt.col1, tt.col2) f FROM (SELECT t1.col1,t1.col2 WHERE t1.type=5) tt
) t2 ON (true);

This seems to work fine, function is only called when it should.

Attempt #4:

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL (
  SELECT t3.* FROM 
  (SELECT t1.col1,t1.col2 WHERE t1.type = 5) tt 
  LEFT JOIN LATERAL function_name(tt.col1, tt.col2) t3 ON (true)) t2 
 ON (true);

This also works fine.

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

2 Answers2