slow variable against int??

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux