-1

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;
4

2 Answers 2

1
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
;
6
  • Thanks for taking the time. As I understand it dbfiddle emulates the execution of a query instead of running it on an actual server. When I run your code on my PostgreSQL server it calls the function every time. I also tried a CASE immediately inside the LEFT JOIN LATERAL, but PostgreSQL doesn't allow that syntax. Apr 12 at 7:41
  • @BjörnMorén As I understand it dbfiddle emulates the execution of a query instead of running it on an actual server. No. The queries are not emulated, they are executed on real (maybe virtual, maybe docker) server. When I run your code on my PostgreSQL server it calls the function every time. SQL is declarative language, and you cannot tell the server "do by this way", you may only try to predict, or look at the execution plan. And it is the very common situation when the same query is executed with different execution plan on different servers or different datasets.
    – Akina
    Apr 12 at 7:46
  • Aha, that is how dbfiddle works. Strange, because I can't get the same result here. Tried it again, but fails. And I suspect that no matter how we write the query, we will never know with 100% certainty how PostgreSQL will execute it. That is why I'm thinking of moving to a NoSQL solution, because even though SQL is a powerful and highly performant language for simple queries, it is also a pain to deal with in complex scenarios. Apr 12 at 10:35
  • @BjörnMorén I suspect that no matter how we write the query, we will never know with 100% certainty how PostgreSQL will execute it. I have already told about Sql language declarativity. PS. See the answer update.
    – Akina
    Apr 12 at 11:01
  • Yes I agree, because of the declarative nature of SQL. So I guess any attempt of rewriting the query is pointless. Instead I will add a check inside the called function and immediately return NULL if the condition is not met. Not the clean solution I wanted, but at least it will perform better. Thanks for your time on this. Apr 12 at 11:41
1

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.

4
  • Thanks for the help. #1 will unfortunately not work, because the main query is very complex. #2 Still called for every row. #3 Same problem. And the suggestion from the previous person doesn't work either. I have double checked the queries for typos. Might it be that my PostgreSQL installation is configured differently? Apr 12 at 11:37
  • 1
    I'm using default configuration on pg 16.1, nothing special. They work on the example query, but I can't say whether it will work on your complicated query without seeing it. If the planner rewrites it for optimization, then maybe or maybe not! Do the queries work on your postgres on the example table? Have you tried setting a high cost to the function? Add "COST 1000" after "LANGUAGE plpgsql"
    – bobflux
    Apr 12 at 12:51
  • Thanks. Apologies, I didn't actually try your code as is, but now when I do, #3 and #4 work fine on my machine. However, when I implement them in my solution I still can't make them work, even if I add COST 1000 to the end of the function. I've wasted enough hours on this (and your time too), so I'll just send a "true" boolean into the function for the rows where it actually should make the costly calculation. A good enough compromise. Looks like I can only select one answer as the solution, and the other guy presented a working solution first so I picked him. Apr 12 at 14:42
  • 1
    No worries lol. Yes the dummy parameter is ugly but... it'll work fine...
    – bobflux
    Apr 12 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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