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??
Date: Thu, 11 May 2006 17:04:02 -0500
Message-ID: <20060511220402.GS99570@pervasive.com> (raw)
In-Reply-To: <200605051646.44060.w.strzelczyk@digitalone.pl>
References: <200605051646.44060.w.strzelczyk@digitalone.pl>
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
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: jnasby@pervasive.com, w.strzelczyk@digitalone.pl
Subject: Re: slow variable against int??
In-Reply-To: <20060511220402.GS99570@pervasive.com>
* 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