Avoid multiple calls to the same function when expanding a composite result

Question

I have an SQL function returning a composite result.

CREATE TYPE result_t AS (a int, b int, c int, d numeric); CREATE OR REPLACE FUNCTION slow_function(int) RETURNS result_t AS $$ -- just some placeholder code to make it slow SELECT 0, 0, 0, ( SELECT sum(ln(i::numeric)) FROM generate_series(1, $1) i ) $$ LANGUAGE sql IMMUTABLE; 

When calling a function, I would like the parts of the composite type to be expanded into several columns. This works fine when I call:

 SELECT (slow_function(i)).* FROM generate_series(0, 200) i abcd ---- ---- ---- -------------------- 0 0 0 (null) 0 0 0 0 0 0 0 0.6931471805599453 0 0 0 1.791759469228055 ... Total runtime: 6196.754 ms 

Unfortunately, this causes the function to be called once per results column, which is unnecessarily slow. This can be verified by comparing the execution time with a query that directly returns a composite result and works four times faster:

 SELECT slow_function(i) FROM generate_series(0, 200) i ... Total runtime: 1561.476 ms 

Sample code is also located at http://sqlfiddle.com/#!15/703ba/7

How can I get a result with multiple columns without losing CPU power?

Answer 1

CTE is not even required. a simple subquery does the job (tested with pg 9.3):

 SELECT i, (f).* -- decompose here FROM ( SELECT i, (slow_func(i)) AS f -- do not decompose here FROM generate_series(1, 3) i ) sub; 

Be sure not to decompose the composite result of the function in the subquery. Reserve this for an external request .
Of course, a well-known type is required. Doesn't work with anonymous posts.

Or what @Richard wrote , LATERAL JOIN works. The syntax could be simpler:

 SELECT * FROM generate_series(1, 3) i, slow_func(i) f 

• LATERAL is used implicitly in Postgres 9.3 or later.
• A function can stand by itself in a FROM ; it does not have to be completed in an additional subselect. Imagine a table in place.

SQL Fiddle with EXPLAIN VERBOSE output for all options. You can see multiple evaluations of the function if this happens.

COST setting

As a rule (it’s not important for this particular request), make sure that you apply the high cost setting to your function, so the planner knows to avoid a more frequent assessment than necessary. How:

 CREATE OR REPLACE FUNCTION slow_function(int) RETURNS result_t AS $func$ -- expensive body $func$ LANGUAGE sql IMMUTABLE COST 100000 ; 

In the documentation:

Larger values ​​cause the planner to try to avoid evaluating the function more often than necessary.

Answer 2

Perhaps a LATERAL subquery is needed.

 SELECT t.id, f.* FROM some_table t, LATERAL (SELECT slow_func(t.id)) f 

This will call the function once for each row, and then "expand" the result into columns in the output. Any subquery will be executed for "expansion", but LATERAL is what allows you to refer to columns from other sentences.

I believe LATERAL was introduced in PostgreSQL 9.3

Answer 3

One way around this problem is to use the WITH clause.

 WITH t AS ( SELECT slow_function(i) AS s FROM generate_series(0, 200) i ) SELECT (s).* FROM t 

This works because the result of the WITH clause materializes before the rest of the query is executed. But this solution is often bad for more complex cases, since it significantly reduces the capabilities of the query optimizer to improve query execution in other ways. Therefore, I am still looking for the best way to solve this problem.