Date: Thu, 11 May 2006 17:04:02 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Witold Strzelczyk <w.strzelczyk@digitalone.pl>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow variable against int??
Message-ID: <20060511220402.GS99570@pervasive.com>
References: <200605051646.44060.w.strzelczyk@digitalone.pl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200605051646.44060.w.strzelczyk@digitalone.pl>
User-Agent: Mutt/1.5.11

If you're trying to come up with ranking then you'll be much happier
using a sequence and pulling from it using an ordered select. See lines
19-27 in http://lnk.nu/cvs.distributed.net/9bu.sql for an example.
Depending on what you're doing you might not need the temp table.

On Fri, May 05, 2006 at 04:46:43PM +0200, Witold Strzelczyk wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461