PostgreSQL Recursive Query Performance

Question

I am a noob when it comes to PostgreSQL, but I was able to get it to produce what I needed it to do which was to take a hierarchy that was up to 30 levels deep, and create a flattened list 'jagged' listview with the topmost level of and every intervening level to each end node. The recursive function, just pushes every parent found, into an array, and then returns the final flattened list for each node using (LIMIT 1)

The following bit of SQL generates the table I need. My question is whether my function that returns the array of values that I use populate the row-columns is called once per row, or is called once for each of the 30 columns in each row.

Can someone guide me to how I would determine that? And/or if it is blatantly obvious that my SQL is inefficient, what might be a better way of putting together the statements.

Thanks in advance for having a look.

DROP FUNCTION IF EXISTS fnctreepath(nodeid NUMERIC(10,0));

CREATE FUNCTION fnctreepath(nodeid NUMERIC(10,0)) 
        RETURNS TABLE (endnode NUMERIC, depth INTEGER, path NUMERIC[]) AS
$$ 
WITH RECURSIVE ttbltreepath(endnode, nodeid, parentid, depth, path) AS (
   SELECT src.nodeid AS endnode, src.nodeid, src.parentid, 1::INT AS depth, 
                 ARRAY[src.nodeid::NUMERIC(10,0)]::NUMERIC(10,0)[] AS path 
      FROM tree AS src WHERE nodeid = $1
UNION
   SELECT ttbl.endnode, src.nodeid, src.parentid, ttbl.depth + 1 AS depth, 
                 ARRAY_PREPEND(src.nodeid::NUMERIC(10,0), ttbl.path::NUMERIC(10,0)[])::NUMERIC(10,0)[] AS path 
      FROM tree AS src, ttbltreepath AS ttbl WHERE ttbl.parentid = src.nodeid
)
SELECT endnode, depth, path FROM ttbltreepath GROUP BY endnode, depth, path ORDER BY endnode, depth DESC LIMIT 1;
$$ LANGUAGE SQL;

DROP TABLE IF EXISTS treepath;

SELECT parentid, nodeid, name
        (fnctreepath(tree.nodeid)).depth, 
               (fnctreepath(tree.nodeid)).path[1] as nodeid01, 
                (fnctreepath(tree.nodeid)).path[2] as nodeid02,
                (fnctreepath(tree.nodeid)).path[3] as nodeid03,
                (fnctreepath(tree.nodeid)).path[4] as nodeid04,
                (fnctreepath(tree.nodeid)).path[5] as nodeid05,
                (fnctreepath(tree.nodeid)).path[6] as nodeid06,
                (fnctreepath(tree.nodeid)).path[7] as nodeid07,
                (fnctreepath(tree.nodeid)).path[8] as nodeid08,
                (fnctreepath(tree.nodeid)).path[9] as nodeid09,
                (fnctreepath(tree.nodeid)).path[10] as nodeid10,
                (fnctreepath(tree.nodeid)).path[11] as nodeid11,
                (fnctreepath(tree.nodeid)).path[12] as nodeid12,
                (fnctreepath(tree.nodeid)).path[13] as nodeid13,
                (fnctreepath(tree.nodeid)).path[14] as nodeid14,
                (fnctreepath(tree.nodeid)).path[15] as nodeid15,
                (fnctreepath(tree.nodeid)).path[16] as nodeid16,
                (fnctreepath(tree.nodeid)).path[17] as nodeid17,
                (fnctreepath(tree.nodeid)).path[18] as nodeid18,
                (fnctreepath(tree.nodeid)).path[19] as nodeid19,
                (fnctreepath(tree.nodeid)).path[20] as nodeid20,
                (fnctreepath(tree.nodeid)).path[21] as nodeid21,
                (fnctreepath(tree.nodeid)).path[22] as nodeid22,
                (fnctreepath(tree.nodeid)).path[23] as nodeid23,
                (fnctreepath(tree.nodeid)).path[24] as nodeid24,
                (fnctreepath(tree.nodeid)).path[25] as nodeid25,
                (fnctreepath(tree.nodeid)).path[26] as nodeid26,
                (fnctreepath(tree.nodeid)).path[27] as nodeid27,
                (fnctreepath(tree.nodeid)).path[28] as nodeid28,
                (fnctreepath(tree.nodeid)).path[29] as nodeid29,
                (fnctreepath(tree.nodeid)).path[30] as nodeid30
INTO treepath
FROM tree;

Answer 1

You should check the volatile attribute of your function.

By default a function is VOLATILE, meaning any call to the function may alter the database, so the query optimiser cannot reuse the result when you use the function several times in the same statement.

Your function is not IMUTABLE, 2+2=4 is immutable. But you should define the STABLE volatility keyword for your function, this way the optimiser could reuse your call of fnctreepath(tree.nodeid) used several time in the same statement as a stable result and share it (run it only once).