From: Witold Strzelczyk <w.strzelczyk@digitalone.pl>
To: pgsql-performance@postgresql.org
Subject: slow variable against int??
Date: Fri, 5 May 2006 16:46:43 +0200
Message-ID: <200605051646.44060.w.strzelczyk@digitalone.pl> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: w.strzelczyk@digitalone.pl
Subject: Re: slow variable against int??
In-Reply-To: <200605051646.44060.w.strzelczyk@digitalone.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox