Weird performance issue with custom function with a for loop.

Hello,

the following function takes forever to execute as is. I see 'Inserting
original actions in temporary table' and nothing after that. If i
replace orig_user_id in the FOR loop with 1811 (the same orig_user_id
passed as the function parameter) it returns immediately correctly (the
table has indices so it's very fast). I am using postgres 8.4.6 on linux
(amd64).


CREATE OR REPLACE FUNCTION validate_test_session(orig_user_id bigint,
orig_start_date timestamp without time zone, orig_end_date timestamp
without time zone)
   RETURNS boolean AS
$BODY$DECLARE
orig_action RECORD;
action_counter BIGINT;
ignored_games INTEGER[];
BEGIN

ignored_games := ARRAY[1,2,7,10,17];

/* Populate tables for test */

/* Populate original session actions */
RAISE NOTICE 'Inserting original actions in temporary table';
action_counter := 0;

FOR orig_action IN (SELECT game_tables.game_type_id,
game_round_actions.table_id, game_round_actions.round_id,
       action_time, action_desc, action_area, amount,
       action_value, seat_id, action_id
     FROM game_round_actions INNER JOIN game_tables ON
game_round_actions.table_id = game_tables.table_id
     WHERE game_round_actions.user_id = orig_user_id AND
game_round_actions.sub_action_id = 0
     AND game_round_actions.action_time BETWEEN orig_start_date AND
orig_end_date
     AND game_tables.game_type_id <> ANY(ignored_games) ORDER BY
action_time, action_id, sub_action_id)

LOOP

RAISE NOTICE 'Found action %', action_counter;

action_counter := action_counter + 1;
END LOOP;

RETURN TRUE;

END;$BODY$
LANGUAGE plpgsql VOLATILE;

--
Regards,

Nicos Panayides
IT Manager

Magneta Technologies Ltd
Tel: +357 22721919, 22317400
Fax: +357 22721917
Web: http://www.magneta.eu