pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow variable against int??
3+ messages / 3 participants
[nested] [flat]
* slow variable against int??
@ 2006-05-05 14:46 Witold Strzelczyk <w.strzelczyk@digitalone.pl>
2006-05-11 22:04 ` Re: slow variable against int?? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 3+ messages in thread
From: Witold Strzelczyk @ 2006-05-05 14:46 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow variable against int??
2006-05-05 14:46 slow variable against int?? Witold Strzelczyk <w.strzelczyk@digitalone.pl>
@ 2006-05-11 22:04 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jim C. Nasby @ 2006-05-11 22:04 UTC (permalink / raw)
To: Witold Strzelczyk <w.strzelczyk@digitalone.pl>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow variable against int??
@ 2006-05-12 16:09 Jim Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jim Nasby @ 2006-05-12 16:09 UTC (permalink / raw)
To: Witold Strzelczyk <w.strzelczyk@digitalone.pl>; +Cc: pgsql-performance
Please cc the list so others can help.
> From: Witold Strzelczyk [mailto:w.strzelczyk@digitalone.pl]
> On Friday 12 May 2006 00:04, you wrote:
>
> Yes, thanks but method is not a point.
Actually, it is a point. Databases don't like doing things procedurally. Using a stored procedure to operate on a set of data is very often the wrong way to go about it. In the case of ranking, I'm extremely doubtful that you'll ever get a procedure to opperate anywhere near as fast as native SQL.
> Can You tell me why
>
> select into inGameRating count(game_result)+1
> from users
> where game_result > 2984;
>
> tooks ~100 ms and
>
> select into inGameRating count(game_result)+1
> from users
> where game_result > inRow.game_result;
>
> where inRow.game_result = 2984 tooks ~1100 ms!?
No, I can't. What's EXPLAIN ANALYZE show?
> btw. I must try your temp sequence but if it is not as quick
> as my new (and
> final) function I'll send if to you.
>
> > 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
>
> --
> Witold Strzelczyk
>
> : : D i g i t a l O n e : : http://www.digitalone.pl
> : : Dowborczykow 25 Lodz 90-019 Poland
> : : tel. [+48 42] 6771477 fax [+48 42] 6771478
>
> ...Where Internet works for effective business solutions...
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-12 16:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-05 14:46 slow variable against int?? Witold Strzelczyk <w.strzelczyk@digitalone.pl>
2006-05-11 22:04 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-12 16:09 Re: slow variable against int?? Jim Nasby <jnasby@pervasive.com>
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