Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0A3452E0047 for ; Wed, 13 Feb 2008 06:59:36 -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 36042-09 for ; Wed, 13 Feb 2008 06:59:24 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id CF43E2E0041 for ; Wed, 13 Feb 2008 06:59:28 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so1499093nfh.23 for ; Wed, 13 Feb 2008 02:59:26 -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=dVv2lT+GhSYaQ5/FAm5Wo/T1TwXX6WJhh8cg9jjUaBc=; b=gx66aoN5ftyG0e4FBOuTPjmgUKBySgXb01j4IYDWQF/qY3bRf5VSftKDO8MuKiXUkFHGULZpAsLdL5J0kLxkqPIBKdbNoRcJ5aGY07NNWIhPxsNvcU8dSTkHyo+fyTN1jBH6//lytaNkmUkCqwPTMhvi4oqI4Ur4XV7ll0TxOho= 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=OFWeHFbCxxE+MyTP8/jyAHlqWy51Prbc7oDeM/rV8CAhPQsFsmzEmWCXaz4GFU/Tb0rUVOhyQ7HHA46mJK2VQ5XDcG7zNXzGaErPtetsT2W9yrE9HzOD5FInq72sLcH7EsB705uj+kKwxVhoDzeFk40cfer88ad+4GRKNpJ1cJ8= Received: by 10.78.185.15 with SMTP id i15mr4619463huf.15.1202900366129; Wed, 13 Feb 2008 02:59:26 -0800 (PST) Received: by 10.78.146.1 with HTTP; Wed, 13 Feb 2008 02:59:26 -0800 (PST) Message-ID: <3caa866c0802130259y26d54baeh457f65d9adc71482@mail.gmail.com> Date: Wed, 13 Feb 2008 15:59:26 +0500 From: "Linux Guru" To: "Tom Lane" Subject: Re: Update with Subquery Performance Cc: pgsql-performance@postgresql.org In-Reply-To: <27294.1202833085@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_923_15734246.1202900366119" References: <3caa866c0802110306rcb5e72dyd264ff330c61f8ae@mail.gmail.com> <2982.1202749162@sss.pgh.pa.us> <3caa866c0802120032r7b7eb672s98d38f1351622424@mail.gmail.com> <27294.1202833085@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/128 X-Sequence-Number: 28670 ------=_Part_923_15734246.1202900366119 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline yes, I also thought of this method and tested it before I got your mail and this solution seems workable. Thanks for the help On Feb 12, 2008 9:18 PM, Tom Lane wrote: > "Linux Guru" writes: > > Analyzing did not help, here is the out of EXPLAIN ANALYZE of update > query > > "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" > > Yeah, that's just not going to be fast. An index on the product column > might help a bit, but the real issue is that you're repetitively > calculating the same aggregates. I think you need a separate temp > table, along the lines of > > create temp table dummy_agg as > select product, > (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100 else 0 end) as s > from dummy > group by product; > > create index dummy_agg_i on dummy_agg(product); -- optional > > update dummy > set gp= (select s from dummy_agg where dummy_agg.product = dummy.product > ); > > The index would only be needed if you expect a lot of rows (lot of > different product values). > > regards, tom lane > ------=_Part_923_15734246.1202900366119 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline yes, I also thought of this method and tested it before I got your mail and this solution seems workable.

Thanks for the help

On Feb 12, 2008 9:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Linux Guru" <linux.binary@gmail.com> writes:
> Analyzing did not help, here is the out of EXPLAIN ANALYZE of update query
> "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"

Yeah, that's just not going to be fast.  An index on the product column
might help a bit, but the real issue is that you're repetitively
calculating the same aggregates.  I think you need a separate temp
table, along the lines of

create temp table dummy_agg as
 select product,
        (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100 else 0 end) as s
 from dummy
 group by product;

create index dummy_agg_i on dummy_agg(product); -- optional

update dummy
 set gp= (select s from dummy_agg where dummy_agg.product = dummy.product);

The index would only be needed if you expect a lot of rows (lot of
different product values).

                       regards, tom lane

------=_Part_923_15734246.1202900366119--