Re: trigger when clause

On Tue, Apr 10, 2012 at 5:10 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> 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.
>

Thanks Jeff. That's very helpful.

--
Andy