Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C9FF32E0339 for ; Tue, 12 Feb 2008 04:46:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 93105-02 for ; Tue, 12 Feb 2008 04:46:40 -0400 (AST) Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.227]) by postgresql.org (Postfix) with ESMTP id E49BE2E0336 for ; Tue, 12 Feb 2008 04:46:37 -0400 (AST) Received: by hu-out-0506.google.com with SMTP id 22so7662928hug.23 for ; Tue, 12 Feb 2008 00:46:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=uInaQ+4q4CbHsjXHwyfoO2zdyXyY6A5+w8EjkfYExkQ=; b=oxPuQ09ovfktGnMsc283ax9WTTrLbACUfcZPVq036hxhU2AzzsrL2polsZwg7pXYdNM3Jx+jE/P5KA5JW+CcayhHX/Sr7ol+H1yEHckUvbQtScP36EP8/85mMuUcxqtJb1N7aSFXVUD0W0zpFTcy92QiatQO+JNUNbEpOHxCWiI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ba7/TUah1zVGRLncjQSzh5gY9Y0h2IJIG6YBgW3CVOTddYiFu+AuvO35ZAac6vgurVA2FOUHk9Tx31nRE5WKX/Zvv/2zuacyCvsMVjSc/sAE7Rt26vqu11IrqvKuCbFS1WiG/frs6qKTqQhRtZqk68RJKW9ZaIEZmpoQtQrnCFc= Received: by 10.78.138.14 with SMTP id l14mr1816985hud.57.1202805994956; Tue, 12 Feb 2008 00:46:34 -0800 (PST) Received: by 10.78.146.1 with HTTP; Tue, 12 Feb 2008 00:46:34 -0800 (PST) Message-ID: <3caa866c0802120046w60f016efx38d3eec2bdd2313e@mail.gmail.com> Date: Tue, 12 Feb 2008 13:46:34 +0500 From: "Linux Guru" To: "Scott Marlowe" Subject: Re: Update with Subquery Performance Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_35985_31245987.1202805994944" References: <3caa866c0802110306rcb5e72dyd264ff330c61f8ae@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/113 X-Sequence-Number: 28655 ------=_Part_35985_31245987.1202805994944 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline See, its calculating sum by grouping the product field. Here is an example Product GP --------- ------- A 30 B 40 A 30 C 50 C 50 Now the query calculates aggregated sum and divide by grouping product so all A's must have same the result, so with B's and C's. > Is this supposed to be updating every single row with one value? > Cause I'm guessing it's running that sub select over and over instead > of one time. > yes you are right that its calculating every time for all elements in each group i.e. GP(A) is calculated twice for A, where it should only calculated once for each group. Is there any way to achieve this? analyze; > set work_mem = 128000; > between the alter and update and see if that helps. that did not help > Also, as Tom said, post explain analyze output of the statement. "Seq Scan on dummy (cost=0.00..56739774.24 rows=23441 width=275) (actual time=18.927..577929.014 rows=22712 loops=1)" " SubPlan" " -> Aggregate (cost=2420.41..2420.43 rows=1 width=19) (actual time= 25.423..25.425 rows=1 loops=22712)" " -> Seq Scan on dummy "temp" (cost=0.00..2416.01 rows=586 width=19) (actual time=0.049..17.834 rows=2414 loops=22712)" " Filter: ((product)::text = ($0)::text)" "Total runtime: 578968.885 ms" Thanks On Feb 12, 2008 2:29 AM, Scott Marlowe wrote: > On Feb 11, 2008 5:06 AM, Linux Guru wrote: > > We have a large datawarehouse stored in postgres and temp tables are > created > > based on user query. The process of temp table creation involves > selecting > > data from main fact table, this includes several select and update > > statements and one of the following update statement is having > performance > > issues. > > > > The newly temp table created for this scenario contains 22712 rows. Here > is > > the query > > > > alter table dummy add column gp numeric(40,15); > > update dummy set gp=(select ( > > case when sum(temp.pd) <> 0 then sum(temp.gd)/sum(temp.pd)*100 else 0 > end ) > > from dummy as temp > > where temp.product=dummy.product) > > Is this supposed to be updating every single row with one value? > Cause I'm guessing it's running that sub select over and over instead > of one time. I'm guessing that with more work_mem the planner might > use a more efficient plan. Try adding > > analyze; > set work_mem = 128000; > between the alter and update and see if that helps. > > Also, as Tom said, post explain analyze output of the statement. > > > > > > Now this query basically updates a table using values within itself in > the > > subquery but it takes tooooo much time i.e. approx 5 mins. The whole > temp > > table creation process is stucked in this query (there are 4 additional > such > > updates with same problem). Index creation is useless here since its > only a > > one time process. > > > > Here is the strip down version (the part making performance issue) of > above > > query i.e. only select statement > > ------------------------------- > > select (case when sum(temp.pd) <> 0 then sum(temp.gd)/sum(temp.pd)*100 > else > > 0 end ) from dummy as temp, dummy as temp2 > > where temp.product=temp2.product group by temp.product > > > > "HashAggregate (cost=1652480.98..1652481.96 rows=39 width=39)" > > " -> Hash Join (cost=1636.07..939023.13 rows=71345785 width=39)" > > " Hash Cond: (("temp".product)::text = (temp2.product)::text)" > > " -> Seq Scan on dummy "temp" (cost=0.00..1311.03 rows=26003 > > width=39)" > > " -> Hash (cost=1311.03..1311.03 rows=26003 width=21)" > > " -> Seq Scan on dummy temp2 (cost=0.00..1311.03rows=26003 > > width=21)" > > ------------------------------- > > > > > > Whats the solution of this problem, or any alternate way to write this > > query? > > > > > > > ------=_Part_35985_31245987.1202805994944 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline See, its calculating sum by grouping the product field. Here is an example

Product      GP
---------      -------
A                  30
B                   40
A                  30
C                 50
C                 50

Now the query calculates aggregated sum and divide by grouping product so all A's must have same the result, so with B's and C's.
Is this supposed to be updating every single row with one value?
Cause I'm guessing it's running that sub select over and over instead
of one time.
yes you are right  that its calculating every time for all elements in each group i.e. GP(A) is calculated twice for A, where it should only calculated once for each group. Is there any  way to achieve this?

analyze;
set work_mem = 128000;
 between the alter and update and see if that helps.
that did not help
 
Also, as Tom said, post explain analyze output of the statement.
 
"Seq Scan on dummy  (cost=0.00..56739774.24 rows=23441 width=275) (actual time=18.927..577929.014 rows=22712 loops=1)"
"  SubPlan"
"    ->  Aggregate  (cost=2420.41..2420.43 rows=1 width=19) (actual time=25.423..25.425 rows=1 loops=22712)"
"          ->  Seq Scan on dummy "temp"  (cost=0.00..2416.01 rows=586 width=19) (actual time=0.049..17.834 rows=2414 loops=22712)"
"                Filter: ((product)::text = ($0)::text)"
"Total runtime: 578968.885 ms"

Thanks

On Feb 12, 2008 2:29 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Feb 11, 2008 5:06 AM, Linux Guru <linux.binary@gmail.com> wrote:
> We have a large datawarehouse stored in postgres and temp tables are created
> based on user query. The process of temp table creation involves selecting
> data from main fact table, this includes several select and update
> statements and one of the following update statement is having performance
> issues.
>
> The newly temp table created for this scenario contains 22712 rows. Here is
> the query
>
> alter table dummy add column gp numeric(40,15);
> update dummy set gp=(select (
> case when sum(temp.pd) <> 0 then sum(temp.gd)/sum(temp.pd)*100 else 0 end  )
> from dummy as temp
>  where temp.product=dummy.product)

Is this supposed to be updating every single row with one value?
Cause I'm guessing it's running that sub select over and over instead
of one time.  I'm guessing that with more work_mem the planner might
use a more efficient plan.  Try adding

analyze;
set work_mem = 128000;
 between the alter and update and see if that helps.

Also, as Tom said, post explain analyze output of the statement.


>
> Now this query basically updates a table using values within itself in the
> subquery but it takes tooooo much time i.e. approx 5 mins. The whole temp
> table creation process is stucked in this query (there are 4 additional such
> updates with same problem). Index creation is useless here since its only a
> one time process.
>
> Here is the strip down version (the part making performance issue) of above
> query i.e. only select statement
> -------------------------------
> select (case when sum(temp.pd) <> 0 then sum(temp.gd)/sum(temp.pd)*100 else
> 0 end  )   from dummy as temp, dummy as temp2
>  where temp.product=temp2.product group by temp.product
>
> "HashAggregate  (cost=1652480.98..1652481.96 rows=39 width=39)"
> "  ->  Hash Join  (cost=1636.07..939023.13 rows=71345785 width=39)"
>  "        Hash Cond: (("temp".product)::text = (temp2.product)::text)"
> "        ->  Seq Scan on dummy "temp"  (cost=0.00..1311.03 rows=26003
> width=39)"
> "        ->  Hash  (cost=1311.03..1311.03 rows=26003 width=21)"
>  "              ->  Seq Scan on dummy temp2  (cost=0.00..1311.03 rows=26003
> width=21)"
> -------------------------------
>
>
> Whats the solution of this problem, or any alternate way to write this
> query?
>
>
>

------=_Part_35985_31245987.1202805994944--