From: Witold Strzelczyk <w.strzelczyk@digitalone.pl>
Organization: Digital One sp. z o.o.
To: pgsql-performance@postgresql.org
Subject: slow variable against int??
Date: Fri, 5 May 2006 16:46:43 +0200
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200605051646.44060.w.strzelczyk@digitalone.pl>

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