Re: Touch row ? - Mailing list pgsql-general

On Jan 23, 2004, at 4:35 AM, Mike Mascari wrote:
>> that will automatically contain  date+time (or likely Unix timestamp)
>> when the row was touched/changed - ie by INSERT or UPDATE ?
>>
>
> CREATE FUNCTION touch() RETURNS trigger AS
<snip>
> CREATE TRIGGER t_foo
<snip>

I was bored this evening and played around with the trigger approach
versus an update-able view via a rule (using PG 7.4).

View/Rule:
create sequence foo_seq;
create table foo(id int4 NOT NULL PRIMARY KEY default
nextval('foo_seq'), d timestamp default now());
insert into foo default values; -- (32k times)
create view foo_view as select * from foo;
create rule foo_view_update_rule as on update to foo_view do instead
    update foo set id = NEW.id, d=now() where foo.id = NEW.id;
-- NOTE: should define INSERT and DELETE rules too

Trigger:
create sequence foo2_seq;
create table foo2(id int4 NOT NULL PRIMARY KEY default
nextval('foo2_seq'), d timestamp default now());
insert into foo2 default values; -- (32k times)
create function foo2_update() returns trugger as 'BEGIN NEW.d = now();
return NEW; END;' language 'plpgsql';
create trigger foo2_update_trigger before update on foo2 for each row
execute procedure foo2_update();


Next, I did some EXPLAIN ANALYZE-ing for updates against "foo_view" and
"foo2":
(I realize my queries are dumb, but this is was just a quick experiment)

explain analyze update foo_view set id = 1 where id = 1;
                                                       QUERY PLAN
------------------------------------------------------------------------
----------------------------------------------
  Nested Loop  (cost=0.00..990.53 rows=26896 width=6) (actual
time=0.060..0.074 rows=1 loops=1)
    ->  Index Scan using idxfooid on foo  (cost=0.00..3.88 rows=164
width=6) (actual time=0.031..0.036 rows=1 loops=1)
          Index Cond: (id = 1)
    ->  Index Scan using idxfooid on foo  (cost=0.00..3.88 rows=164
width=0) (actual time=0.007..0.015 rows=1 loops=1)
          Index Cond: (id = 1)
Average runtime for 10 executions:  0.165ms

explain analyze update foo2 set id = 1 where id = 1;
                                                     QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------
  Index Scan using idxfoo2id on foo2  (cost=0.00..3.88 rows=164
width=14) (actual time=0.031..0.039 rows=1 loops=1)
    Index Cond: (id = 1)
Average runtime for 10 executions: 0.328ms


So the view/rule trick is nearly twice as fast as using a trigger.  The
down-side (if you really want to call it that) is you're "forced" to
use the view instead of the table for access to the data, and you're
forced to manually maintain the "do instead" part of the rules.

However, considering the seemingly near lack of overhead involved in
views (and apparently rules), combined with the extra layer of
abstraction views provide, this seems like a more efficient and
flexible approach.  Plus, it still gives you the ability to use
triggers on the underlying table for more complicated tasks.  A
real-world example could prove all this wrong, but it's really cool to
see a 2x performance improvement for something simple.

One thing I did notice however, is that if you have lots of rows, you
*really* need an index on the primary key column (which you get by
default) in order for the view/rule to win.

eric