Home > mailing lists

Thread: number of values updated per column

number of values updated per column

From

Willy-Bas Loos

Date:

17 January 2013, 20:36:47

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

Re: number of values updated per column

From

Akihiro Okuno

Date:

17 January 2013, 21:30:19

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