On Tue, Apr 10, 2012 at 5:10 PM, Jeff Davis <pg...@j-davis.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
