Re: 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