slow variable against int??

Поиск
Список
Период
Сортировка
От Witold Strzelczyk
Тема slow variable against int??
Дата
Msg-id 200605051646.44060.w.strzelczyk@digitalone.pl
обсуждение исходный текст
Ответы Re: slow variable against int??  ("Jim C. Nasby" <jnasby@pervasive.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Gregory Stewart"
Дата:
Сообщение: Re: Performance Issues on Opteron Dual Core
Следующее
От: Kenneth Marshall
Дата:
Сообщение: Re: performance question (something to do w/