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