On 23 December 2012 19:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On 21 December 2012 16:51, Kevin Grittner <kgrittn@mail.com> wrote:
>>> If none, and this is strictly an optimization, what are the benchmarks
>>> showing?
>
>> AFAIK its well known that a check constraint is much faster than a
>> trigger.
>
> I don't believe that that's "well known" at all, at least not for
> apples-to-apples comparison cases. A C-coded BEFORE trigger doesn't
> have very much overhead; I suspect it's probably comparable to
> expression evaluation setup overhead. I think if you want to argue
> for this on performance grounds, you need to actually prove there's
> a significant performance advantage, not just assume there will be.
If you want to see some tests, I'm sure those can be arranged, no
problem. But honestly, if its low enough, then which is the fastest
will likely be moot in comparison with the cost of a non-C coded
role-based security check. So I think our attention is best spent on
providing a few likely C-coded security checks, so we're able to
address the whole performance concern not just the constraint/trigger
debate.
That still leaves the points about ensuring the trigger/checks are
executed last and also that they are added automatically, rather than
requiring them to be added manually. As KaiGai points out, if they are
added automatically, it doesn't really matter which we pick.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services