Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 07463841FF3 for ; Wed, 25 Apr 2012 18:45:57 -0300 (ADT) Received: from nm4-vm1.bullet.mail.ne1.yahoo.com ([98.138.91.44]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SNA25-0008Hy-1M for pgsql-performance@postgresql.org; Wed, 25 Apr 2012 21:45:56 +0000 Received: from [98.138.90.50] by nm4.bullet.mail.ne1.yahoo.com with NNFMP; 25 Apr 2012 21:45:39 -0000 Received: from [209.191.108.96] by tm3.bullet.mail.ne1.yahoo.com with NNFMP; 25 Apr 2012 21:45:39 -0000 Received: from [66.94.237.120] by t3.bullet.mud.yahoo.com with NNFMP; 25 Apr 2012 21:45:39 -0000 Received: from [127.0.0.1] by omp1025.access.mail.mud.yahoo.com with NNFMP; 25 Apr 2012 21:45:39 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 107283.6814.bm@omp1025.access.mail.mud.yahoo.com Received: (qmail 66892 invoked by uid 60001); 25 Apr 2012 21:45:38 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1335390338; bh=XVXvbQE7lTm4qcyG7Ttb8uWPHjTwlNFq0l27ckAU9WQ=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=AsEkdqJQAu0WGkrwQ4yQPbh8h3E/ehvdeTqJFMC4qT8gqksOEJsTCO17ThOxVVy6TyMvexOMQ8mWBhuL1gmCI9ztM8J0hP7TH3YXU0j75WtlbHbPFZsOqbtRE46SfZJJyZ7bZATz1K0seBHIHziGSTSxyhIdbSsZmgWdK6IG0qo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=3gqiheN2O2VCWbjrzKO3gOv+ssssYz/OlyN4NYIqgTZuQkFyj5NZDfX8y9BDKotyL+lo18HtIRyzbMRZfBzCe5y0eDCYxdgn3l9EX2S/bKGd8YC0KgNEehwm/sl3j/cabwKMmrgTvvW7pL4wDMTsEkczjPhqJuESw8PSEON23Xs=; X-YMail-OSG: MQz4PnkVM1lSApJDJzklwfgzk4SNZuOUJSzK61V_rFseBDv w7SSEtiMUi_GaqG79M06BABExGLbUguoS2ghZG.M95RI8OppbDUfJFT3c6EV pVtFmeXzj1xlR3VxdR7BHOzhAZpWstT9QH0VdYpmRyfzDAwZHlInvZcZhzfe __xcyUkLNur81dW62p9JvZ80V250DKFdlY7V37HIZwN6TJpaZSRg.UfawHh9 GQFlsBedw_TH.xil.6ymkq94mbAwu2JieTr1n2dKDu6baKq7VYwbBJ9H9C_b ZgVczzVcDq2lUap1OA8.4O.I5MjvvYg1Zaj5z0qaywNLbWyfkG.G96Kk35b0 CQ0jE6lRlqzK.1P50ji5t9rj4cQTaAS9_byflLAVLkJd5vB2UKmZ1P379f3O NLqvI8tLSg.7M2unW3FU3mAJ4C7DjDd10O627cIqdY8JGto_4TPLbxfN5w5z v34PX0ua9yOxhCy9zDR.oT0tF2bE3HilKCYAOH5hm_sgNgtkv5ylFGqwqSvb 3FAuGoHS0C_.IyqrIVAS_q_XbMe0ZDa8- Received: from [24.6.62.191] by web184505.mail.ne1.yahoo.com via HTTP; Wed, 25 Apr 2012 14:45:38 PDT X-Mailer: YahooMailWebService/0.8.117.340979 References: <1335379923.47084.YahooMailNeo@web184514.mail.ne1.yahoo.com> <1335388323.84458.YahooMailNeo@web184517.mail.ne1.yahoo.com> Message-ID: <1335390338.10325.YahooMailNeo@web184505.mail.ne1.yahoo.com> Date: Wed, 25 Apr 2012 14:45:38 -0700 (PDT) From: Venki Ramachandran Reply-To: Venki Ramachandran Subject: Re: Parallel Scaling of a pgplsql problem To: Pavel Stehule Cc: Samuel Gendler , "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1957974866-1078875740-1335390338=:10325" X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201204/150 X-Sequence-Number: 46796 --1957974866-1078875740-1335390338=:10325 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Replacing current_timestamp() with=A0transaction_timestamp() and=A0statemen= t_timestamp() did not help!!!.=A0=0A=0AMy timestamp values are still the sa= me. I can't believe this is not possible in PG. In oracle you can =A0use 's= elect sysdate from dual' and insert that value and you can see which sql st= atement or function is taking a long time during development mode. All I wa= nt to do is to find where my pgplsql code is spending its 11 seconds. The o= verall pseudo code is as follows:=0A=0Awhile end_date >=3D start_date=0Asel= ect some_columns INTO vars1 from tbl-1 where emp_id =3D 'first emp';=0Asele= ct some_columns INTO vars2 from tbl-1 where emp_id =3D 'second emp';=0ADo s= ome computation with vars1 and vars2;=0Aselect calc_comp(emp_1, emp_2) into= v_profit;=0Aend while=0A=0AI want to see for each select and each call to = the function what the system date is (or something else) which will tell me= where the 11 seconds is being spent. How do I do that?=0A=0AThanks, Venki= =0A=A0 =A0 =A0 =A0=A0=0A=0A=0A________________________________=0A From: Pav= el Stehule =0ATo: Venki Ramachandran =0ACc: Samuel Gendler ; "pgsql= -performance@postgresql.org" =0ASent: We= dnesday, April 25, 2012 2:26 PM=0ASubject: Re: [PERFORM] Parallel Scaling o= f a pgplsql problem=0A =0AHello=0A=0Ahttp://www.postgresql.org/docs/8.1/sta= tic/functions-datetime.html=0A=0ACURRENT_TIME and CURRENT_TIMESTAMP deliver= values with time zone;=0ALOCALTIME and LOCALTIMESTAMP deliver values witho= ut time zone.=0A=0ACURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIM= ESTAMP can=0Aoptionally take a precision parameter, which causes the result= to be=0Arounded to that many fractional digits in the seconds field. Witho= ut a=0Aprecision parameter, the result is given to the full available=0Apre= cision.=0A=0ASome examples:=0A=0ASELECT CURRENT_TIME;=0AResult: 14:39:53.66= 2522-05=0A=0ASELECT CURRENT_DATE;=0AResult: 2001-12-23=0A=0ASELECT CURRENT_= TIMESTAMP;=0AResult: 2001-12-23 14:39:53.662522-05=0A=0ASELECT CURRENT_TIME= STAMP(2);=0AResult: 2001-12-23 14:39:53.66-05=0A=0ASELECT LOCALTIMESTAMP;= =0AResult: 2001-12-23 14:39:53.662522=0A=0ASince these functions return the= start time of the current=0Atransaction, their values do not change during= the transaction. This=0Ais considered a feature: the intent is to allow a = single transaction=0Ato have a consistent notion of the "current" time, so = that multiple=0Amodifications within the same transaction bear the same tim= e stamp.=0A=0A=A0 =A0 Note: Other database systems might advance these valu= es more frequently.=0A=0APostgreSQL also provides functions that return the= start time of the=0Acurrent statement, as well as the actual current time = at the instant=0Athe function is called. The complete list of non-SQL-stand= ard time=0Afunctions is:=0A=0Atransaction_timestamp()=0Astatement_timestamp= ()=0A=0ARegards=0A=0APavel Stehule=0A=0A2012/4/25 Venki Ramachandran :=0A> Another question (probably a silly mistake) w= hile debugging this problem:=0A> I put in insert statements into the pgplsq= l code to collect the=0A> current_timestamp and see where the code was spen= ding most of it time....The=0A> output is as follows:=0A>=0A> =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 log_text=0A> =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |=0A> =A0insertdatetime=0A= > -------------------------------------------------------------------------= ------------------------------------------------------------------------+--= -----------------------------=0A> =A0INPUT=0A> VARIABLES,Src,emp_id_1,emp_i= d_100,StartDate,2012-01-01,EndDate,2012-02-01,Tou,DOnPk,CRR=0A> Type,ptp-ob= ligations,MinPositivePirce,1.00 | 2012-04-25 20:56:42.691965+00=0A> =A0Star= ting get_DAM_Value function=0A>=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2012-04-25= 20:56:42.691965+00=0A> =A0StartDate,2012-01-01 01:00:00,EndDate,2012-02-02= 00:00:00=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 |=0A> 2012-04-25 20:56:42.691965+00=0A> =A0StartHr,vSrcprice,vSink= price,vDiff,vAvgValue,vTotalDAMValue,vPkTotal,vOffPkTotal=0A> =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2012-04-25=0A> 20:56:42.691965+00= =0A> =A02012-01-01=0A> 01:00:00,18.52,16.15,-2.370000,-2.370000,0.000000,0.= 000000,-2.370000=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 2012-04-25=0A= > 20:56:42.691965+00=0A> =A02012-01-01=0A> 02:00:00,17.22,15.60,-1.620000,-= 1.620000,0.000000,0.000000,-3.990000=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 | 2012-04-25=0A> 20:56:42.691965+00=0A> =A02012-01-01=0A> 03:00:00,18.= 22,17.55,-0.670000,-0.670000,0.000000,0.000000,-4.660000=0A> =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 | 2012-04-25=0A> 20:56:42.691965+00=0A> =A02012-01= -01=0A> 04:00:00,18.53,18.13,-0.400000,-0.400000,0.000000,0.000000,-5.06000= 0=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 2012-04-25=0A> 20:56:42.69196= 5+00=0A> =A0...=0A> =A0...=0A> =A0...=0A> =A0...=0A> =A02012-02-02=0A> 00:0= 0:00,2,17.13,17.13,0.000000,0.000000,0.000000,-7940.250000,-5216.290000=0A>= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2012-04-25=0A> 20:56:42.691965= +00=0A> =A02012-02-02=0A> 00:00:00,3,16.54,16.54,0.000000,0.000000,0.000000= ,-7940.250000,-5216.290000=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 20= 12-04-25=0A> 20:56:42.691965+00=0A> =A02012-02-02=0A> 00:00:00,4,16.27,16.2= 7,0.000000,0.000000,0.000000,-7940.250000,-5216.290000=0A> =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0| 2012-04-25=0A> 20:56:42.691965+00=0A> =A0TotalPEA= KMVal,-7940.250000,NumIntervals,2034,AvgRTMVAlue,-3.903761=0A> =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A> 2012-04-25 20:56:= 42.691965+00=0A> =A0OUTPUT=0A> VARIABLES,AvgPurValue,-2.84,AvgSaleValue,-3.= 90,Profit,-1.06,ProfitPercentage,-106.00=0A> =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= | 2012-04-25=0A> 20:56:42.691965+00=0A> (3832 rows)=0A>=0A> Why doesn't th= e current_timestamp value change within the pgpsql code? For=0A> every sele= ct from a table to compute, I am inserting into my debug_log dummy=0A> tabl= e. For all 3832 rows I have the same current_timestamp value which I was=0A= > hoping to get by the following insert statement:=0A> insert into debug_lo= g ('some log text', current_timestamp);=0A>=0A> But when I run this procedu= re from the psql command line, it takes 11=0A> seconds....I gave a bigger d= ate range than what I stated as (40 ms) in my=0A> first post hence the chan= ge to 11 seconds.=0A>=0A> dev=3D> select=0A> calc_comp('emp_id_1','emp_id_1= 00','DAM-RTM',to_date('2012-01-01','yyyy-mm-dd'),to_date('2012-02-01','yyyy= -mm-dd'),'DOnPk','ptp-obligations',1.00,=0A> 0);=0A> =A0 =A0 =A0 =A0 =A0crr= _valuation=0A> -------------------------------=0A> =A00||-2.84|-3.90|-1.06|= -106.00|=0A> (1 row)=0A>=0A> Time: 11685.828 ms.=0A>=0A> The last input val= ue 1/0 while calling the above function controls, if any=0A> log line messa= ges should be inserted or not, 0=3Dinsert, 1 =3D do_not_insert.=0A> When I = toggle the flag the overall timing did not change. Does it not some=0A> tim= e in ms to write 3832 rows to a table?=0A> Why is my current_timestamp valu= e not changing. I was expecting the=0A> difference between the last row's t= imestamp value MINUS the first row's=0A> tiemstamp value to equal my 11.685= seconds. What is wrong here?=0A>=0A>=0A> -Venki=0A>=0A> __________________= ______________=0A> From: Samuel Gendler =0A>=0A>= To: Venki Ramachandran =0A> Cc: "pgsql-perfo= rmance@postgresql.org" =0A> Sent: Wednesd= ay, April 25, 2012 12:36 PM=0A>=0A> Subject: Re: [PERFORM] Parallel Scaling= of a pgplsql problem=0A>=0A>=0A>=0A> On Wed, Apr 25, 2012 at 11:52 AM, Ven= ki Ramachandran=0A> wrote:=0A>=0A> Hi all:= =0A> Can someone please guide me as to how to solve this problem? If this i= s the=0A> wrong forum, please let me know which one to post this one in. I = am new to=0A> Postgres (about 3 months into it)=0A>=0A> I have PostGres 9.0= database in a AWS server (x-large) and a pgplsql program=0A> that does som= e computation. It takes in a date range and for one pair of=0A> personnel (= two employees in a company) it calculates some values over the=0A> time per= iod. It takes about 40ms (milli seconds) to complete and give me the=0A> an= swer. All good so far.=0A>=0A> Now I have to run the same pgplsql on all po= ssible combinations of employees=0A> and with 542 employees that is about s= ay 300,000 unique pairs.=0A>=0A> So (300000 * 40)/(1000 * 60 * 60) =3D 3.33= hours and I have to rank them and=0A> show it on a screen. No user wants t= o wait for 3 hours, =A0they can probably=0A> wait for 10 minutes (even that= is too much for a UI application). How do I=0A> solve this scaling problem= ? Can I have multiple parellel sessions and each=0A> session have multiple/= processes that do a pair each at 40 ms and then=0A> collate the results. Do= es PostGres or pgplsql have any parallel computing=0A> capability.=0A>=0A>= =0A> The question is, how much of that 40ms is spent performing the calcula= tion,=0A> how much is spent querying, and how much is function call overhea= d, and how=0A> much is round trip between the client and server with the qu= ery and results?=0A> =A0Depending upon the breakdown, it is entirely possib= le that the actual=0A> per-record multiplier can be kept down to a couple o= f milliseconds if you=0A> restructure things to query data in bulk and only= call a single function to=0A> do the work. =A0If you get it down to 4ms, t= hat's a 20 minute query. =A0Get it=0A> down to 1ms and you're looking at on= ly 5 minutes for what would appear to be=0A> a fairly compute-intensive rep= ort over a relatively large dataset.=0A>=0A>=0A>=0A> --1957974866-1078875740-1335390338=:10325 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Replacing current_t= imestamp() with transaction_timestamp() and&nb= sp;statement_timestamp() did not help!!!. 

My ti= mestamp values are still the same. I can't believe this is not possible in = PG. In oracle you can  use 'select sysdate from dual' and insert that value = and you can see which sql statement or function is taking a long time durin= g development mode. All I want to do is to find where my pgplsql code is sp= ending its 11 seconds. The overall pseudo code is as follows:
<= div style=3D"font-family: tahoma, 'new york', times, serif; font-size: 10pt= ; ">
while end_date = >=3D start_date
=09select some_columns INTO vars1= from tbl-1 where emp_id =3D 'first emp';
=09select some_columns INTO vars2 from tbl-1 where emp_id =3D 'second em= p';
=09Do some computation with vars1 and vars2;
=09select calc_comp(emp_1, emp_2) into v_profit;
e= nd while

I want to see for each select and each call to the function what the= system date is (or something else) which will tell me where the 11 seconds= is being spent. How do I do that?

Thanks, Venki
        


From: Pa= vel Stehule <pavel.stehule@gmail.com>
To: Venki Ramachandran <venki_ramachandran@yahoo.c= om>
Cc: Samuel Gendler <sgendler@id= easculptor.com>; "pgsql-performance@postgresql.org" <pgsql-performanc= e@postgresql.org>
Sent: Wednesday, April 25, 2012 2:26 PM
Subject: Re: [PERFORM] Parallel Scaling of a pgplsql prob= lem

=0AHello

http://www.postgresql.org/docs/8= .1/static/functions-datetime.html

CURRENT_TIME and CURRENT_TIMESTAMP= deliver values with time zone;
LOCALTIME and LOCALTIMESTAMP deliver val= ues without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, a= nd LOCALTIMESTAMP can
optionally take a precision parameter, which cause= s the result to be
rounded to that many fractional digits in the seconds= field. Without a
precision parameter, the result is given to the full a= vailable
precision.

Some examples:

SELECT CURRENT_TIME;Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-= 23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-0= 5

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05<= br>
SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

= Since these functions return the start time of the current
transaction, = their values do not change during the transaction. This
is considered a feature: the intent= is to allow a single transaction
to have a consistent notion of the "cu= rrent" time, so that multiple
modifications within the same transaction = bear the same time stamp.

    Note: Other database systems= might advance these values more frequently.

PostgreSQL also provide= s functions that return the start time of the
current statement, as well= as the actual current time at the instant
the function is called. The c= omplete list of non-SQL-standard time
functions is:

transaction_t= imestamp()
statement_timestamp()

Regards

Pavel Stehule
=
2012/4/25 Venki Ramachandran <venki_ramachandra= n@yahoo.com>:
> Another question (probably a silly mistake) wh= ile debugging this problem:
> I put in insert statements into the pgplsql code to collect the
> current_timestamp and see where the co= de was spending most of it time....The
> output is as follows:
>= ;
>                   &n= bsp;                     =                      = ;       log_text
>           =                      = ;                     &nb= sp;               |
>  insert= datetime
> ----------------------------------------------------------= ---------------------------------------------------------------------------= ------------+-------------------------------
>  INPUT
> VARIABLES,Src,emp_id_1,emp_id_100,StartDate,2012-01-01,EndDate,2012-02-01,= Tou,DOnPk,CRR
> Type,ptp-obligations,MinPositivePirce,1.00 | 2012-04-= 25 20:56:42.691965+00
>  Starting get_DAM_Value function
>=
>              | 2012-04-25 20:56= :42.691965+00
>  StartDate,2012-01-01 01:00:00,EndDate,2012-02-0= 2 00:00:00
>                 =                      = ;                     &nb= sp;             |
> 2012-04-25 20:56:42= .691965+00
>  StartHr,vSrcprice,vSinkprice,vDiff,vAvgValue,vTota= lDAMValue,vPkTotal,vOffPkTotal
>           &= nbsp;                    =                                | 2012-04-25
> 20:56= :42.691965+00
>  2012-01-01
> 01:00:00,18.52,16.15,-2.3700= 00,-2.370000,0.000000,0.000000,-2.370000
>        = ;                     &nb= sp;                     &= nbsp;     | 2012-04-25
> 20:56:42.691965+00
>  2= 012-01-01
> 02:00:00,17.22,15.60,-1.620000,-1.620000,0.000000,0.00000= 0,-3.990000
>                =                     &nbs= p;                   | 2012-04= -25
> 20:56:42.691965+00
>  2012-01-01
> 03:00:00,18= .22,17.55,-0.670000,-0.670000,0.000000,0.000000,-4.660000
>   &n= bsp;                                       &nbs= p;             | 2012-04-25
> 20:56:42.= 691965+00
>  2012-01-01
> 04:00:00,18.53,18.13,-0.400000,-= 0.400000,0.000000,0.000000,-5.060000
>         &n= bsp;                     =                      = ;     | 2012-04-25
> 20:56:42.691965+00
>  ...>  ...
>  ...
>  ...
>  2012-02-= 02
> 00:00:00,2,17.13,17.13,0.000000,0.000000,0.000000,-7940.250000,-= 5216.290000
>                =                     &nbs= p;                    | 2= 012-04-25
> 20:56:42.691965+00
>  2012-02-02
> 00:00:00,3,16.54,16.54,0.000000,0.000000,0.000000= ,-7940.250000,-5216.290000
>            = ;                     &nb= sp;                     &= nbsp;  | 2012-04-25
> 20:56:42.691965+00
>  2012-02-0= 2
> 00:00:00,4,16.27,16.27,0.000000,0.000000,0.000000,-7940.250000,-5= 216.290000
>                 =                      = ;                    | 20= 12-04-25
> 20:56:42.691965+00
>  TotalPEAKMVal,-7940.25000= 0,NumIntervals,2034,AvgRTMVAlue,-3.903761
>       &nbs= p;                     &n= bsp;                                       &nbs= p;|
> 2012-04-25 20:56:42.691965+00
>  OUTPUT
> VARI= ABLES,AvgPurValue,-2.84,AvgSaleValue,-3.90,Profit,-1.06,ProfitPercentage,-1= 06.00
>                  = ;                     &nb= sp;             | 2012-04-25
> 20:56:42= .691965+00
> (3832 rows)
>
> Why doesn't the current_time= stamp value change within the pgpsql code? For
> every select from a = table to compute, I am inserting into my debug_log dummy
> table. For= all 3832 rows I have the same current_timestamp value which I was
> = hoping to get by the following insert statement:
> insert into debug_= log ('some log text', current_timestamp);
>
> But when I run th= is procedure from the psql command line, it takes 11
> seconds....I gave a bigger date range than what I stated as (40 ms) in my<= br>> first post hence the change to 11 seconds.
>
> dev=3D&g= t; select
> calc_comp('emp_id_1','emp_id_100','DAM-RTM',to_date('2012= -01-01','yyyy-mm-dd'),to_date('2012-02-01','yyyy-mm-dd'),'DOnPk','ptp-oblig= ations',1.00,
> 0);
>          crr_val= uation
> -------------------------------
>  0||-2.84|-3.90= |-1.06|-106.00|
> (1 row)
>
> Time: 11685.828 ms.
>=
> The last input value 1/0 while calling the above function controls= , if any
> log line messages should be inserted or not, 0=3Dinsert, 1= =3D do_not_insert.
> When I toggle the flag the overall timing did n= ot change. Does it not some
> time in ms to write 3832 rows to a tabl= e?
> Why is my current_timestamp value not changing. I was expecting = the
> difference between the last row's timestamp value MINUS the first row's
> tiemstamp value to equal my 11.685 seconds. What is wr= ong here?
>
>
> -Venki
>
> __________________= ______________
> From: Samuel Gendler <sgendler@ide= asculptor.com>
>
> To: Venki Ramachandran <venki_ramachandran@yahoo.com>
> Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
> Sent: Wednesday= , April 25, 2012 12:36 PM
>
> Subject: Re: [PERFORM] Parallel S= caling of a pgplsql problem
>
>
>
> On Wed, Apr 25,= 2012 at 11:52 AM, Venki Ramachandran
> <venki_ramach= andran@yahoo.com> wrote:
>
> Hi all:
> Can someone= please guide me as to how to solve this problem? If this is the
> wr= ong forum, please let me know which one to post this one in. I am new to> Postgres (about 3 months into it)
>
> I have PostGres 9.0= database in a AWS server (x-large) and a pgplsql program
> that does= some computation. It takes in a date range and for one pair of
> per= sonnel (two employees in a company) it calculates some values over the
&= gt; time period. It takes about 40ms (milli seconds) to complete and give m= e the
> answer. All good so far.
>
> Now I have to run th= e same pgplsql on all possible combinations of employees
> and with 5= 42 employees that is about say 300,000 unique pairs.
>
> So (300000 * 40)/(1000 * 60 * 60) =3D 3.33 hours and I have to rank them and<= br>> show it on a screen. No user wants to wait for 3 hours,  they = can probably
> wait for 10 minutes (even that is too much for a UI ap= plication). How do I
> solve this scaling problem? Can I have multipl= e parellel sessions and each
> session have multiple/processes that d= o a pair each at 40 ms and then
> collate the results. Does PostGres = or pgplsql have any parallel computing
> capability.
>
><= br>> The question is, how much of that 40ms is spent performing the calc= ulation,
> how much is spent querying, and how much is function call = overhead, and how
> much is round trip between the client and server = with the query and results?
>  Depending upon the breakdown, it = is entirely possible that the actual
> per-record multiplier can be k= ept down to a couple of milliseconds if you
> restructure things to query data in bulk and only call a single function to
> do the wo= rk.  If you get it down to 4ms, that's a 20 minute query.  Get it=
> down to 1ms and you're looking at only 5 minutes for what would ap= pear to be
> a fairly compute-intensive report over a relatively larg= e dataset.
>
>
>
>


--1957974866-1078875740-1335390338=:10325--