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 > [EMAIL PROTECTED] > > ---------------------------(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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
