slow variable against int??

From: Witold Strzelczyk <w(dot)strzelczyk(at)digitalone(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow variable against int??
Date: 2006-05-05 14:46:43
Message-ID: 200605051646.44060.w.strzelczyk@digitalone.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)strzelczyk(at)gmail(dot)com

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message mcelroy, tim 2006-05-05 14:48:01 Re: Memory and/or cache issues?
Previous Message mcelroy, tim 2006-05-05 14:45:21 Re: Memory and/or cache issues?