Trigger vs Rule

From: Ключников А(dot)С(dot) <alexs(at)analytic(dot)mv(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: Trigger vs Rule
Date: 2006-04-02 08:31:49
Message-ID: 20060402083149.GA62166@mail.analytic.mv.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all.

There are two tables:

create table device_types (
id int,
name varchar
);
about 1000 rows

create table devices (
id int,
type int REFERENCES device_types(id),
name varchar,
data float
);
about 200000 rows

And about 1000 functions:
create function device_type1(int) returns ..
create function device_type2(int) returns ..
...
create function device_type1000(int) returns ..

What is faster?

One trigger with 1000 ELSE IF
if old.type=1 then
select device_type1(old.id);
else if old.type=2 then
select device_type2(old.id);
...
else if old.type=1000 then
select device_type1000(old.id);
end if;

Or 1000 rules
create rule device_type1 AS ON update to devices
where old.type=1
DO select device_type1(old.id);
create rule device_type2 AS ON update to devices
where old.type=2
DO select device_type2(old.id);
...
create rule device_type1000 AS ON update to devices
where old.type=1000
DO select device_type1000(old.id);

thx.

--
С уважением,
Ключников А.С.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Qingqing Zhou 2006-04-02 14:33:05 Re: statistics buffer is full
Previous Message Tom Lane 2006-04-02 04:46:09 Re: Query using SeqScan instead of IndexScan