Re: [SQL] How do I optimize this?
Wei Weng wrote: I made a mistake in the queries: They should be update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 1) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 2) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 10) as b where tkey = value; You should be able to generate all the counts from one scan: UPDATE T set t1 = b.a1, t2 = b.a2 ... FROM ( SELECT sum(CASE WHEN D.da=1 THEN 1 ELSE 0 END) AS a1, sum(CASE WHEN D.da=2 THEN 1 ELSE 0 END) AS a2, ... FROM D ) AS b WHERE tkey = value You might also want to look at the crosstab functions in the tablefunc contrib module (see appendix F of the manuals). -- Richard Huxton Archonet Ltd - Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do I optimize this?
Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; The queries are run on the same value. Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do I optimize this?
I made a mistake in the queries: They should be update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 1) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 2) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = 10) as b where tkey = value; Thanks Wei On 03/17/2009 05:43 PM, Wei Weng wrote: Hi all. I have the following scenario: A table T (int t1; int t2; ... int t10; int tkey) A table D (int da; int db), And I have the following query update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; ... update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da = count) as b where tkey = value; The queries are run on the same value. Is there anyway to optimize this??? Thanks Wei -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
