Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvuEG-0006ZZ-Io for pgsql-sql@arkaria.postgresql.org; Thu, 17 Jan 2013 18:30:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TvuEG-0005rR-19 for pgsql-sql@arkaria.postgresql.org; Thu, 17 Jan 2013 18:30:20 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvuEE-0005qM-GX for pgsql-sql@postgresql.org; Thu, 17 Jan 2013 18:30:18 +0000 Received: from mail-pb0-f49.google.com ([209.85.160.49]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvuEC-0004u6-O9 for pgsql-sql@postgresql.org; Thu, 17 Jan 2013 18:30:17 +0000 Received: by mail-pb0-f49.google.com with SMTP id un15so1548213pbc.22 for ; Thu, 17 Jan 2013 10:30:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:content-type:mime-version:subject:from:in-reply-to:date :cc:message-id:references:to:x-mailer; bh=G0PUMxfcbFy76u4aC4m2qPD1iMMP3A8oIH2PG2QeJC8=; b=i5ffDTBmass2afUDR1iKZCL5Nf5txVzWfkxNAS3COrePxAPEz5XMBYtsSqOat//IDE k5cmkN8CzlmeK0UCDqyysVHvOJvXQ+Vl4I9dMFsIZ+jKHW7cq9YcbQKfpSygkmUrtDbb RDsBHYeWnx80GpPydLyCkw6snpPip911oKP2y26zbikxmm7Rx/GCxHpxE9fYHPiBxxpr CFiKyyjEdUls9ol09M4KQHQJ3sa5jvMuvWNLFsF2i+IoLObpK/wUQ/fMrdvBonRXfftF taFtghwvIVPV5091ngNtkA/7h6uEB0KOiEvcBnr08LfGMd82k4P12nckyy+GJw2mM2uy v5IA== X-Received: by 10.66.73.164 with SMTP id m4mr16106551pav.12.1358447415625; Thu, 17 Jan 2013 10:30:15 -0800 (PST) Received: from [10.0.0.42] (w0109-49-133-103-243.uqwimax.jp. [49.133.103.243]) by mx.google.com with ESMTPS id hs2sm1483508pbc.22.2013.01.17.10.30.12 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 17 Jan 2013 10:30:14 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_7674AD57-3D14-408F-B042-75F6B7612916" Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\)) Subject: Re: number of values updated per column From: Akihiro Okuno In-Reply-To: Date: Fri, 18 Jan 2013 03:30:09 +0900 Cc: pgsql-sql@postgresql.org Message-Id: References: To: Willy-Bas Loos X-Mailer: Apple Mail (2.1499) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail=_7674AD57-3D14-408F-B042-75F6B7612916 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=iso-8859-1 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 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 --Apple-Mail=_7674AD57-3D14-408F-B042-75F6B7612916 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1 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 = >=3D 60 THEN 1 END) AS a_count,
        = count(CASE WHEN tab1.b >=3D 60 THEN 1 END) AS = b_count,
        count(CASE WHEN tab1.c = >=3D 60 THEN 1 END) AS c_count
    = FROM
        = tab1
),
upd AS (
    UPDATE tab1 = SET
        a =3D CASE WHEN tab1.a >=3D = 60 THEN -1 ELSE tab1.a END,
        b =3D = CASE WHEN tab1.b >=3D 60 THEN -1 ELSE tab1.b END,
  =       c =3D CASE WHEN tab1.c >=3D 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 <willybas@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=3Dcase when tab1.a >=3D 60 then -1 else tab1.a = end
        , b=3Dcase when tab1.b = >=3D 60 then -1 else tab1.b end
        , c=3Dcase when tab1.c >=3D 60 = then -1 else tab1.c end
    from tab1 old =
    where old.id=3Dtab1.id
    = returning
        case when tab1.a !=3D = old.a then 1 else 0 end as a_upd
        , case when tab1.b !=3D old.b then = 1 else 0 end as b_upd
        , case = when tab1.c !=3D 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

= --Apple-Mail=_7674AD57-3D14-408F-B042-75F6B7612916--