Re: [GENERAL] trigger when clause
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
[GENERAL] trigger when clause
Hi, Does anyone know the time complexity of the algorithm used to handle triggers with a when clause? 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. What if n is 10? What if n is 200? The when clause would just be comparing a text predicate column for equality. Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
