Re: trigger when clause

On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote:

> Does anyone know the time complexity of the algorithm used to handle
> triggers with a when clause?

It's done with a linear scan of all triggers, testing the WHEN clause
for each.

> To make this a little more concrete, what is likely to perform better
>
>
> a) A single trigger with "n" if/else clauses
> b) A set of "n" triggers each using a different when clause.

Both are essentially linear.

If you want to scale to a large number of conditions, I would recommend
using one trigger in a fast procedural language, and searching for the
matching conditions using something better than a linear search.

To beat a linear search, you need something resembling an index, which
is dependent on the types of conditions. For instance, if your
conditions are:

00 <= x < 10
10 <= x < 20
20 <= x < 30
...

you can use a tree structure. But, obviously, postgres won't know enough
about the conditions to know that a tree structure is appropriate from a
given sequence of WHEN clauses. So, you should use one trigger and code
the condition matching yourself.

Regards,
Jeff Davis