From: Akihiro Okuno <choplin.choplin@gmail.com>
To: Willy-Bas Loos <willybas@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: number of values updated per column
Date: Fri, 18 Jan 2013 03:30:09 +0900
Message-ID: <C0D21CCA-5315-4DC3-A7A7-018E10DA6EBD@gmail.com> (raw)
In-Reply-To: <CAHnozThQL7Ws9hnjQSo3D70WWy4qN_DBmLoEs+aUVZxONHdTtQ@mail.gmail.com>
References: <CAHnozThQL7Ws9hnjQSo3D70WWy4qN_DBmLoEs+aUVZxONHdTtQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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 <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=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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: choplin.choplin@gmail.com, willybas@gmail.com
Subject: Re: number of values updated per column
In-Reply-To: <C0D21CCA-5315-4DC3-A7A7-018E10DA6EBD@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox