X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4D4F09FA490 for ; Fri, 12 May 2006 13:09:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 02604-03 for ; Fri, 12 May 2006 13:09:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id 08A0D9FA1B0 for ; Fri, 12 May 2006 13:09:32 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Fri, 12 May 2006 11:09:28 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Fri, 12 May 2006 11:09:27 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 12 May 2006 11:09:27 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: slow variable against int?? Date: Fri, 12 May 2006 11:09:27 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099F55@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] slow variable against int?? thread-index: AcZ1moQ4Qqf963lQSqG9GhPLKxaolgAQ4jnQ From: "Jim Nasby" To: "Witold Strzelczyk" Cc: "Pgsql-Performance \(E-mail\)" X-OriginalArrivalTime: 12 May 2006 16:09:27.0374 (UTC) FILETIME=[717B66E0:01C675DE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/327 X-Sequence-Number: 19114 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: >=20 > 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=20 >=20 > select into inGameRating count(game_result)+1=20 > from users > where game_result > 2984; >=20 > tooks ~100 ms and >=20 > select into inGameRating count(game_result)+1=20 > from users > where game_result > inRow.game_result; >=20 > where inRow.game_result =3D 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=20 > as my new (and=20 > final) function I'll send if to you. >=20 > > 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=20 > 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=20 > rating by game > > > result. This isn't probably a perfect solution but I have=20 > 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=20 > 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 :=3D -1; > > > inGameRating int :=3D -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 =3D 2984 this is=20 > very slow ~ 1100 ms! > > > select into inGameRating count(game_result)+1=20 > from users > > > where game_result > inRow.game_result; > > > */ > > > inGameResult :=3D inRow.game_result; > > > end if; > > > > > > if inGameResult > inRow.game_result then > > > inGameRating :=3D inGameRating + 1; > > > end if; > > > > > > inRow.game_rating :=3D inGameRating; > > > inGameResult :=3D inRow.game_result; > > > return next inRow; > > > > > > end loop; > > > return; > > > end; > > > $$; > > > -- > > > Witold Strzelczyk > > > witek.strzelczyk@gmail.com > > > > > > ---------------------------(end of=20 > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore=20 > your desire to > > > choose an index scan if your joining column's=20 > datatypes do not > > > match >=20 > --=20 > Witold Strzelczyk >=20 > =A0 : : =A0 D i g i t a l =A0O n e =A0: : =A0http://www.digitalone.pl > =A0 : : =A0 Dowborczykow 25 =A0Lodz =A090-019 =A0Poland > =A0 : : =A0 tel. [+48 42] 6771477 =A0fax [+48 42] 6771478 >=20 > =A0 =A0...Where Internet works for effective business solutions... >=20