> MySQL had the following syntax available:
> `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP
I wonder supporting this syntax would speed things up a little bit.
Here's a simple benchmark about the situation we are discussing here:
There are 2 tables:
CREATE TABLE t1 (n integer not null, mtime timestamp with time
zone not null);
CREATE TABLE t2 (n integer not null, mtime timestamp with time
zone not null);
and a trigger for the second one:
CREATE LANGUAGE plpgsql;
CREATE FUNCTION touch() RETURNS trigger AS $$
BEGIN
new.mtime := now();
RETURN new;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER ttt_mtime BEFORE UPDATE or INSERT
ON t2 FOR EACH ROW EXECUTE PROCEDURE touch();
and here's the actual test:
test=> INSERT INTO t1(n,mtime) SELECT *, now() FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 7382.313 ms
test=> INSERT INTO t2(n) SELECT * FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 24541.088 ms
So, updating the column explicitly is 3.5 times faster than the
trigger. My guess is that in real life applications where tables have
"bigger" rows (more columns, data types other than integer), the
overhead of the trigger will be even smaller.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
