Re: Odd performance difference in check constraint : SQL(slow) vs plpgsql(fast)

bubba postgres <bubba(dot)postgres(at)gmail(dot)com> writes:
> This is the reverse of what I thought I would find.
> In short my check constraint is extracting the epoch from a start timestamp,
> and an end timestamp to get the number of seconds difference.
> It then uses this number to check the array_upper() of an array to make sure
> it's the proper size

> The SQL version uses a case statement, and the plpgsql uses an IF/ELSE

> In a particular insert test
> The plpgsql version adds 1 second over the no constraints case.
> the sql version adds 10 seconds over the no constraints case.

> Why would this be?

It would not likely be faster unless it can be inlined, and maybe not
even then, because of plan caching effects (plpgsql is a lot better
about that). In this particular case, I'm suspicious whether all the
operations are immutable; if they aren't, the marking of the function
as immutable will definitely prevent inlining.

regards, tom lane