|
Hello all, long time no chit-chat on the PG mailing list.
We’re upgrading from 8.0.3 to 8.3 and found that some stored procedures
utilizing int_agg that we had left over from 7.3 had terrible
performance. No problem, using ANY() we’re able to regain that
performance, more or less, and at the same time greatly simplify our stored procedures.
But things can never be fast enough, can they? So I have a question or
two. Here’s my function for reference: CREATE OR REPLACE FUNCTION bg_nodes2descendants(INT[], INT)
RETURNS SETOF connection_generation AS ' DECLARE _row connection_generation%ROWTYPE; _children INT[]; BEGIN -- this is faster than constructing in the loop below --_children = array(SELECT connectee_node_id FROM
connections WHERE connection_type_id = 1 AND connector_node_id = ANY($1)); FOR _row IN SELECT connection_id, connection_type_id,
connector_node_id, connector_node_type_id, connectee_node_id, connectee_node_type_id,
current, timestamp, $2 + 1 FROM connections
WHERE connection_type_id = 1 AND connector_node_id = ANY($1) LOOP _children :=
_children || _row.connectee_node_id; RETURN NEXT _row; END LOOP; IF FOUND THEN RETURN QUERY SELECT * FROM
bg_nodes2descendants(_children, $2+1); END IF; RETURN; END ' LANGUAGE 'plpgsql'; So, my concern is alluded to in the
comment above. When I use this function in places where it returns large
results, building the _children array directly (in the commented out line) is
about 25% faster. But I’d like to avoid building the children array
altogether and would instead like to generate that array from the already
collected output rows. For example, right before the recursive call, I’d
like to select a column of the buffered output rows, cast it to an integer[],
and pass it into the recursive call. Is there an internal value I can
access for this such as: _children := array(SELECT connectee_node_id FROM $output); Bonus question - if I rewrite the first FOR loop as: RETURN QUERY SELECT connection_id, connection_type_id,
connector_node_id, connector_node_type_id, connectee_node_id, connectee_node_type_id,
current, timestamp, $2 + 1 FROM connections
WHERE connection_type_id = 1 AND connector_node_id = ANY($1); I get “ERROR: structure of query does not match
function result type”, even though the type signatures of the returned
columns match the “connection_generation” rowtype. I am pretty
sure this could be resolved by casting the resulting columns to that row type,
but I am lost as to how the syntax to do such a thing would look. Thanks in advance for the help, and keep up the great
work. PG8.3 is an amazing piece of software and it blows me away how much
more advanced it gets with every release. Bart Grantham VP of R&D Logicworks Inc. – Complex and Managed Hosting |
