We are using a number of stored procedures that are called often from our client programs. I include one here as an example. The problem we are seeing is that when executing some of these that deal with a large number of records, they begin execution and never return. The process handling the request chews 97% of CPU resources and must be cancelled before it will release. We have tried examining where in the process it stops via the debug output, but this has proven to be fruitless as A. the problem doesn't happen with all the SP's (some other SP's deal with far more data but don't have the problem), and B. doesn't always happen consistently with the SP's that seem to cause problems. What we do know is that the machine doesn't seem to be low on memory, never returns any error, and happens regardless of machine or PG version (we've tried 7.4.1, and 7.4.2).
Wouldn't the following query be functionally the same as the procedure you posted (if you fix the rate and the groupid)? If so, does it perform better and how does the explain look?
UPDATE user_sessions
SET cost_bytes_in = a.costIn,
cost_bytes_out = a.costOut
FROM (
SELECT
session_id,
CASE
WHEN (us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 < 0.0001 THEN 0
ELSE (us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 END
AS costIn,
CASE
WHEN (us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 < 0.0001 THEN 0
ELSE (us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 END
AS costOut
FROM user_session
WHERE group_id = $groupId
) a
WHERE group_id = $groupId AND user_id = a.user_id;
Jochem
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
