Re: Weird performance issue with custom function with a for loop.

Nicos Panayides <nicos(at)magneta(dot)com(dot)cy> writes:
> 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).

It seems likely that you're getting a different plan for the generic
case because that user id isn't representative of the overall average
for the column. You could investigate by explaining a parameterized
query:

PREPARE foo (bigint) AS
SELECT ... WHERE game_round_actions.user_id = $1 ... ;
EXPLAIN EXECUTE foo(1811);

(To really see exactly what's happening, you'd probably need to
parameterize for each of the plpgsql variables used in the query;
I'm suspicious that the BETWEEN might be contributing to the
issue as well.)

Possibly increasing the stats target for the user id column would help,
but it's hard to be sure without knowing what its distribution is like.

regards, tom lane