slow variable against int??
| От | Witold Strzelczyk |
|---|---|
| Тема | slow variable against int?? |
| Дата | |
| Msg-id | 200605051646.44060.w.strzelczyk@digitalone.pl обсуждение |
| Ответы |
Re: slow variable against int??
|
| Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: