pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

number of values updated per column
2+ messages / 2 participants
[nested] [flat]

* number of values updated per column
@ 2013-01-17 17:36  Willy-Bas Loos <willybas@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Willy-Bas Loos @ 2013-01-17 17:36 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: number of values updated per column
@ 2013-01-17 18:30  Akihiro Okuno <choplin.choplin@gmail.com>
  parent: Willy-Bas Loos <willybas@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Akihiro Okuno @ 2013-01-17 18:30 UTC (permalink / raw)
  To: Willy-Bas Loos <willybas@gmail.com>; +Cc: pgsql-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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-01-17 18:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-17 17:36 number of values updated per column Willy-Bas Loos <willybas@gmail.com>
2013-01-17 18:30 ` Akihiro Okuno <choplin.choplin@gmail.com>

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