How about separating count query from update statement. I found a bit performance improvement from your example.
with 100000 rows, fastest time in 10 times try yours: 989.679 ms mine: 719.739 ms query ------- (same DDL, DML) WITH cnt AS ( SELECT count(CASE WHEN tab1.a >= 60 THEN 1 END) AS a_count, count(CASE WHEN tab1.b >= 60 THEN 1 END) AS b_count, count(CASE WHEN tab1.c >= 60 THEN 1 END) AS c_count FROM tab1 ), upd AS ( UPDATE tab1 SET a = CASE WHEN tab1.a >= 60 THEN -1 ELSE tab1.a END, b = CASE WHEN tab1.b >= 60 THEN -1 ELSE tab1.b END, c = CASE WHEN tab1.c >= 60 THEN -1 ELSE tab1.c END ) select a_count, b_count, c_count from cnt ; On 2013/01/18, at 2:36, Willy-Bas Loos <willy...@gmail.com> wrote: > Hi, > > I'd like to know, per column, how many values were changed by my query. > I have a working example, but i am curious what you people think about it. > Is it efficient? I have to make a self join, but i don't see a faster way. > > > Here's the example: > ------------- > drop table if exists tab1 ; > create table tab1(id serial primary key, a integer, b integer, c integer); > insert into tab1 (a,b,c) > select x*random(), x*random(), x*random() > from generate_series(0,100) foo(x); > > with foo as ( > update tab1 set > a=case when tab1.a >= 60 then -1 else tab1.a end > , b=case when tab1.b >= 60 then -1 else tab1.b end > , c=case when tab1.c >= 60 then -1 else tab1.c end > from tab1 old > where old.id=tab1.id > returning > case when tab1.a != old.a then 1 else 0 end as a_upd > , case when tab1.b != old.b then 1 else 0 end as b_upd > , case when tab1.c != old.c then 1 else 0 end as c_upd > ) > select 'a' as fieldname, sum(a_upd) as updates from foo > union all > select 'b' as fieldname, sum(b_upd) as updates from foo > union all > select 'c' as fieldname, sum(c_upd) as updates from foo > ------------- > > Cheers, > > WBL > -- > "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth