Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 441D82E008D for ; Mon, 11 Feb 2008 07:07:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 84252-04 for ; Mon, 11 Feb 2008 07:06:44 -0400 (AST) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.187]) by postgresql.org (Postfix) with ESMTP id 6026F2E009A for ; Mon, 11 Feb 2008 07:06:49 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so5229569fka.3 for ; Mon, 11 Feb 2008 03:06:47 -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:mime-version:content-type; bh=/z24Z67kMUUgJ5KrL3X8fQZEHC74t8+UORSdGPk+Ako=; b=EfFfOdRQz4d9NXIdg7O2PxKd39V8h7IdlWF9ZnUQGGdsKrEzWTb02ctuKLafcjnEEhCxmjrWXEQ+w3+7n2fZaZFSCe1Jcbr5a4DjSrfshd7GLAfRAGsr+zsmseM5C/0bLNPdnFK86PrHF1RUQ6y3rNZb/8P+eGKZrTDgQxoD4LM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=fvjpLGql7sXqQiwIbOTwkku9QKn1iZV5A92Dj4PagrgJjbY5pi/J4ZGSY+zf23VR4POP8PI4edbMCqMsGMrFDkQsYRDCBvL5vs4kvAkpOp/ExoKogTV+Yd5lTNWT37KyDNfymfubdM0sgk9WIx+pyF84INmXobdXZEPFESVbf5Y= Received: by 10.78.132.2 with SMTP id f2mr28490619hud.19.1202728004301; Mon, 11 Feb 2008 03:06:44 -0800 (PST) Received: by 10.78.146.1 with HTTP; Mon, 11 Feb 2008 03:06:44 -0800 (PST) Message-ID: <3caa866c0802110306rcb5e72dyd264ff330c61f8ae@mail.gmail.com> Date: Mon, 11 Feb 2008 16:06:44 +0500 From: "Linux Guru" To: pgsql-performance@postgresql.org Subject: Update with Subquery Performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29138_23596165.1202728004293" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/97 X-Sequence-Number: 28639 ------=_Part_29138_23596165.1202728004293 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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) 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_29138_23596165.1202728004293 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)

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_29138_23596165.1202728004293--