I have a question about my function. I must get user rating by game result.
This isn't probably a perfect solution but I have one question about
select into inGameRating count(game_result)+1 from users
where game_result > inRow.game_result;
This query in function results in about 1100 ms.
inRow.game_result is a integer 2984
And now if I replace inRow.game_result with integer
select into inGameRating count(game_result)+1 from users
where game_result > 2984;
query results in about 100 ms
There is probably a reason for this but can you tell me about it because I
can't fine one
My function:
create or replace function ttt_result(int,int) returns setof tparent_result
language plpgsql volatile as $$
declare
inOffset alias for $1;
inLimit alias for $2;
inRow tparent_result%rowtype;
inGameResult int := -1;
inGameRating int := -1;
begin
for inRow in
select
email,wynik_gra
from
konkurs_uzytkownik
order by wynik_gra desc limit inLimit offset inOffset
loop
if inGameResult < 0 then -- only for first iteration
/* this is fast ~100 ms
select into inGameRating
count(game_result)+1 from users
where game_result > 2984;
*/
/* even if inRow.game_result = 2984 this is very slow ~ 1100 ms!
select into inGameRating count(game_result)+1 from users
where game_result > inRow.game_result;
*/
inGameResult := inRow.game_result;
end if;
if inGameResult > inRow.game_result then
inGameRating := inGameRating + 1;
end if;
inRow.game_rating := inGameRating;
inGameResult := inRow.game_result;
return next inRow;
end loop;
return;
end;
$$;
--
Witold Strzelczyk
witek.strzelczyk@gmail.com
