From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Andy Chambers <achambers(at)mcna(dot)net> |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org>, Kenneth Tilton <ktilton(at)mcna(dot)net> |
Subject: | Re: trigger when clause |
Date: | 2012-04-10 21:10:18 |
Message-ID: | 1334092218.13416.81.camel@sussancws0025 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lukas Eklund | 2012-04-10 22:24:41 | Re: Resize numeric column without changing data? |
Previous Message | Aaron Burnett | 2012-04-10 21:03:43 | Re: Pg 9.1.3 pg_crypto question |